Is there a way to return non existent values when using groupby and union all?

I have a table that I’m trying to return summarized results from for a chart that will have two datasets, one for debit transactions that have happened each month and one for payments that have happened each month. The table I’m querying looks like this:

ID  amount type     created_date
1   200    Debit    2020-02-14
2   150    Payment  2020-02-15
3   200    Payment  2020-02-21
4   100    Debit    2020-03-01
5   100    Debit    2020-03-05
6   50     Payment  2020-04-01
7   150    Payment  2020-04-02

What I’m looking to get back out of my query is a result that does a sum(amount) once for each type and unions them together so that the result looks something like this:

month     debit_sum  payment_sum
February  200        350
March     200        0
April     0          200

So far I’ve gotten to the below query which returns mostly the right data but in the wrong format.

SELECT MONTHNAME(created_date) as month_name, type as type_name, sum(amount) as amount
FROM
(
    SELECT created_date, amount, type
    FROM balance_adjustments
    WHERE type = 'Payment'
    AND created_date > '2020-01-01'
    GROUP BY MONTHNAME(created_date)
    UNION ALL
    SELECT created_date, amount, type
    FROM balance_adjustments
    WHERE type = 'Debit'
    AND created_date > '2020-01-01'
    GROUP BY MONTHNAME(created_date)
) AS adjustments
GROUP BY type, MONTHNAME(created_date) 
ORDER BY created_date;

This returns the below, which you can see returns one row for each type, but doesn’t return a row if there is no values to sum. I could work with this format if I could have it return 2 rows for each month (one for each type) and if there is no values return 0.

month     type_name  amount
February  Debit      200
February  Payment    350
March     Debit      200
April     Payment    200

What I can’t figure out is if there is a way to either return the above with non-existent values getting filled in with 0 or if there is a way to do sum(amount) twice, once for each type as separate columns.

Answer

If I follow you correctly, you can use conditional aggregation:

select extract(year_month from created_date) created_month,
    sum(case when type = 'Debit'   then amount else 0 end) as debit_sum,
    sum(case when type = 'Payment' then amount else 0 end) as payment_sum
from balance_adjustments
where created_at >= '2020-01-01'
group by created_month

I used year_month instead of the name of the month, since you probably want to not count the same month in different years together. You can change that to something else if you want otherwise.

Advertisement

Answer