windc windc - 2 months ago 9
SQL Question

SQL command to sum up data group by date

I have a database that contain:

+----+---------------------+-------------+-----------------+
| id | date | pay| usr_type |
+----+---------------------+-------------+-----------------+
| 1 | 2016-10-17 | 500| boss|
| 2 | 2016-10-17 | 200| boss|
| 3 | 2016-10-24 | 300| user|
| 4 | 2016-10-24 | 100| user|
+----+---------------------+-------------+-----------------+


I want the results something like this:

+---------------------+-------------+-----------------+
| date | user| boss|
+----+---------------------+--------+-----------------+
| 2016-10-17 | 0| 700|
| 2016-10-24 | 400| 0|
+----+---------------------+-------------+------------+

Answer

Here's one option using conditional aggregation:

select date, 
    sum(case when usr_type = 'user' then pay else 0 end) user,
    sum(case when usr_type = 'boss' then pay else 0 end) boss
from yourtable
group by date