Select total and full total on one line

Question

Table X

AccountId	InvoiceId	SeasonId	AmountSold
1	I1	S1	5
1	I1	S1	5
1	I1	S2	6
1	I2	S1	8

I need this; filter on AccountId = 1, Group By SeasonId (no problem) but also the "Full Total Per InvoiceId"

AccountId	InvoiceId	SeasonId	AmountSold	TotalInvoice
1	I1	S1	10	16
1	I1	S2	6	16
1	I2	S2	8	8

Answer 1

You can aggregate by acount/invoice/season first, then use window functions to compute the grand total per invoice:

select accountId, invoiceId, seasonId, 
    sum(amountSold) amountSold,
    sum(sum(amountSold)) over(partition by accountId, invoiceId) totalInvoice
from mytable
group by accountId, invoiceId, seasonId

Of course if you want to filter on a given account id, we can use a where clause (and then it its not needed anymore to group and partition by that):

select invoiceId, seasonId, 
    sum(amountSold) amountSold,
    sum(sum(amountSold)) over(partition by invoiceId) totalInvoice
from mytable
where accountId = 1
group by invoiceId, seasonId