CODEWITHSUNDEEP
mysqlsql
Windsooon
Windsooon

Reputation: 7110

How to use group by column depending on other column

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.

Upvotes: 10

Views: 295

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

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?

Upvotes: 14

Related Questions