Group data by foreign key and date with total by date

I need help to select daily payments made and group by the organization and date. Group by date, then the total number of payments and the sum total amount of payments for each day Tables are as follows,

organizations 
-----------------------------
|    id       |    name     |
+-------------+-------------+
|    1        |    org_1    |
+-------------+-------------+
|    2        |    org_2    |
+-------------+-------------+
|    3        |    org_2    |
-----------------------------

payments
------------------------------------------------------------
|   id      |  org_id    |    amount   |    date_created   |
+-----------+------------+-------------+-------------------+
|    1      |    2       |    20       |   2020-11-06      |
+-----------+------------+-------------+-------------------+
|    2      |    2       |    10       |   2020-11-06      |
+-----------+------------+-------------+-------------------+
|    3      |    1       |    50       |   2020-11-05      |
+-----------+------------+-------------+-------------------+
|    4      |    2       |    10       |   2020-11-05      |
------------------------------------------------------------

Expected Result

----------------------------------------------------------------------------------------------
| date_created   |   total_amount |   num_payments    |  org_1    |    org_2    |    org_3   |
+----------------+----------------+-------------------+-----------+-------------+------------+
|   2020-11-06   |     30.00      |      2            |    0      |     2       |    0       |
+----------------+----------------+-------------------+-----------+-------------+------------+
|   2020-11-05   |     60.00      |      2            |    1      |     1       |    0       |
+----------------+----------------+-------------------+-----------+-------------+------------+

Answer

Use conditional aggregation:

select p.date_created,
    sum(p.amount) as total_amount,
    count(*) as num_payments,
    sum(case when o.name = 'org_1' then p.amount else 0 end) as org_1,
    sum(case when o.name = 'org_2' then p.amount else 0 end) as org_2,
    sum(case when o.name = 'org_3' then p.amount else 0 end) as org_3
from payments p
inner join organizations o on o.id = p.org_id
group by p.date_created

Advertisement

Answer