How to use group by column depending on other column

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.

Solutions/Answers:

Solution 1:

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?

References