Skip to content
Advertisement

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       |
+----------------+----------------+-------------------+-----------+-------------+------------+

Advertisement

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
3 People found this is helpful
Advertisement