SUM DISTINCT but keeping all entries

[ben0109]

Hi,

I need to sum the values of all fields that has the same batch number - preferably in my load script. I have tried GROUP BY, but I still need all entries, so this does not work. I simply want to sum the amount, if the batch number is the same.

Below is an example of my table:

Type	Amount	Batch
Sale	100	B123
Sale	522	B153
Payment	156	B631
Sale	1684	B123
Payment	648	B631
Payment	6218	B631

My end goal will be to have a table like the example below:

Type	Amount	Batch	New Field
Sale	100	B123	1784
Sale	522	B153	522
Payment	156	B631	7022
Sale	1684	B123	1784
Payment	648	B631	7022
Payment	6218	B631	7022

Any help will be of great value.

Cheers,

Ben

[tresesco · Accepted solution]

Try like:

t1:
Load * Inline [
Type Amount Batch
Sale 100 B123
Sale 522 B153
Payment 156 B631
Sale 1684 B123
Payment 648 B631
Payment 6218 B631] (delimiter is spaces);

Join
Load
    Batch,
    Sum(Amount) as BatchSales
Resident t1 Group By Batch;

[ben0109 · Accepted solution]

Hi Tresesco

Thanks for your reply. It seems to work.

I have also stumbled upon another solution that can also work:

SUM(Amount) OVER(PARTITION BY Batch) AS [Batch Sales].

Cheers