Skip to content
Advertisement

How calculate filtered by date aggregate sum after grouping by some column in SQL?

I have a table which contains payments received by person per date. I.e.

person  paymentDate  money
--------------------------
Smith   01-01-2020   567
Black   01-01-2020   780

We know how to calculate aggregate sum in SQL:

select person,sum(money) from personIncomeByDate group by person

We know how to calculate aggregated sum for some period of dates:

select person,sum(money) 
from personIncomeByDate 
where paymentDate = '12-12-2020' 
group by person

Here is the question: what if I need last aggregate but I dont want to remove persons which never had payments on 12-12-2020? In other words, after grouping and summing I want to see something like that

person | moneySumFor12-12-2020
------------------------------
Smith  | 300
Black  | 0
King   | 245

Probably I can achieve this with join, but this looks like an overhead. Any options?

Advertisement

Answer

You can try to use operator CASE:

SELECT person, 
SUM (CASE WHEN paymentDate='12-12-2020' THEN money ELSE 0 END)moneySum
FROM personIncomeByDate
GROUP BY person

If you need to get several dates then use predicate IN:

SELECT person, 
SUM (CASE WHEN paymentDate IN ('12-12-2020', '10-12-2020'...) THEN money ELSE 0 END)moneySum
FROM personIncomeByDate
GROUP BY person
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement