Menu
Here is my table trans:
id // user_id // money // type // date
1 1001 20 1 2015-11-1
2 1001 50 1 2015-11-1
3 1001 50 2 2015-11-2
4 1001 50 2 2015-11-3
5 1002 10 2 2015-11-3
6 1002 20 2 2015-11-1
7 1002 70 3 2015-11-1
8 1003 80 1 2015-11-2
9 1003 10 1 2015-11-3
10 1003 20 1 2015-11-3
I want output like:
id // user_id // sum_money_1 // sum_money_2 // sum_money_3 // date
1 1001 10 10 20 2015-11-1
2 1001 20 10 30 2015-11-2
3 1001 30 10 10 2015-11-3
4 1002 90 20 10 2015-11-1
5 1002 20 40 20 2015-11-3
6 1003 80 20 30 2015-11-2
7 1003 30 10 10 2015-11-3
Which means sum money group by every day, every type and display by each id.
I tried this:
SELECT a.id, a.user_id,
(SELECT sum(a1.money) FROM trans a1 where a1.type = 1) AS sum_type1,
(SELECT sum(a2.money) FROM trans a2 where a2.type = 1) AS sum_type2,
(SELECT sum(a3.money) FROM trans a3 where a3.type = 3) AS sum_type3,
a.date FROM trans a group by a.user_id,a.date
But it failed, and returns like:
id // user_id // sum_money_1 // sum_money_2 // sum_money_3 // date
1 1001 90 70 80 2015-11-1
2 1001 90 70 80 2015-11-2
3 1001 90 70 80 2015-11-3
4 1002 90 70 80 2015-11-1
5 1002 90 70 80 2015-11-3
6 1003 90 70 80 2015-11-2
7 1003 90 70 80 2015-11-3
It sum all the money by every user.
220
Select Home > Group by. In the Group by dialog box, select Advanced to select more than one column to group by. To add another column, select Add Grouping.
Usage of Group By Multiple ColumnsWe can use the group by multiple-column technique to group multiple records into a single record. All the records with the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple-column technique.
GROUP BY clause is used with the SELECT statement. In the query, GROUP BY clause is placed after the WHERE clause. In the query, GROUP BY clause is placed before ORDER BY clause if used any.
The Group By statement is used to group together any rows of a column with the same value stored in them, based on a function specified in the statement. Generally, these functions are one of the aggregate functions such as MAX() and SUM(). This statement is used with the SELECT command in SQL.
You can use SUM and CASE WHEN:
SELECT `user_id` , `date`,
SUM(CASE WHEN `type` = 1 THEN `money` ELSE 0 END) AS sum_money_1,
SUM(CASE WHEN `type` = 2 THEN `money` ELSE 0 END) AS sum_money_2,
SUM(CASE WHEN `type` = 3 THEN `money` ELSE 0 END) AS sum_money_3
FROM your_table
GROUP BY user_id, `date`
ORDER BY user_id, `date`;
Warning:
How your id column should be generated?
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
