CODEWITHSUNDEEP
mysqlsqlsumpivotaggregate-functions
Wells
Wells

Reputation: 142

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

Upvotes: 0

Views: 110

Answers (1)

GMB
GMB

Reputation: 222482

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

Upvotes: 1

Related Questions