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