从多个表中按列SELECT和按ID分组

Question

I have the following data

purchase_rec_a

+----------+------------+----------------+
| user     | date       | total          |
+----------+------------+----------------+
| 1510     | 2018-08-08 | 5              |
| 1510     | 2018-09-12 | 10             |
| 1510     | 2018-09-19 | 15             |
+----------+------------+----------------+

purchase_rec_b

+----------+------------+----------------+
| user     | date       | total          |
+----------+------------+----------------+
| 1510     | 2018-05-08 | 10             |
| 1510     | 2018-07-02 | 15             |
+----------+------------+----------------+

I can LEFT JOIN both tables by following sql

SELECT u.id, a.date, a.total AS total_a, b.date, b.total AS 
total_b
FROM users AS u
LEFT JOIN purchase_rec_a AS a ON u.id = a.user
LEFT JOIN purchase_rec_b AS b ON u.id = b.user
WHERE u.id =1510

and I get the following

+-------+------------+---------+------------+---------+
| id    | date       | total_a | date       | total_b |
+-------+------------+---------+------------+---------+
| 1510  | 2018-08-08 | 5       | 2018-05-08 | 10      |
| 1510  | 2018-08-08 | 5       | 2018-07-02 | 15      |
| 1510  | 2018-09-12 | 10      | 2018-05-08 | 10      |
| 1510  | 2018-09-12 | 10      | 2018-07-02 | 15      |
| 1510  | 2018-09-19 | 15      | 2018-05-08 | 10      |
| 1510  | 2018-09-19 | 15      | 2018-07-02 | 15      |
+-------+------------+---------+------------+---------+

so what I'm trying to do here is to group them in distinctive way by total_a and total_b and group by user so I tried

SELECT u.id, a.date, SUM( a.total ) AS total_a, 
b.date, SUM( b.total ) AS total_b
FROM users AS u
LEFT JOIN purchase_rec_a AS a ON u.id = a.user
LEFT JOIN purchase_rec_b AS b ON u.id = b.user
WHERE u.id =1510

and the result is

+------+------------+---------+------------+---------+
| id   | date       | total_a | date       | total_b |
+------+------------+---------+------------+---------+
| 1510 | 2018-08-08 | 60      | 2018-05-08 | 75      |
+------+------------+---------+------------+---------+

but the problem here is some data are duplicated and they are added as total

my expected result are below

+------+------------+---------+------------+---------+
| id   | date       | total_a | date       | total_b |
+------+------------+---------+------------+---------+
| 1510 | 2018-08-08 | 30      | 2018-05-08 | 25      |
+------+------------+---------+------------+---------+

I can get the above result by multiple SQL but I want it in single SQL, is it possible?

Answer 1

If you want to put the data "side-by-side", I would suggest union all and group by:

select user, date, sum(a_total) as a_total, sum(b_total) as b_total
from ((select user, date, total as a_total, 0 as b_total
       from purchase_rec_a
      ) union all
      (select user, date, 0 as a_total, total as b_total
       from purchase_rec_b
      )
     ) ab
group by user, user_date;

If you don't want date in the result, you can use the same structure, just changing the outer query:

select user, sum(a_total) as a_total, sum(b_total) as b_total
from ((select user, date, total as a_total, 0 as b_total
       from purchase_rec_a
      ) union all
      (select user, date, 0 as a_total, total as b_total
       from purchase_rec_b
      )
     ) ab
group by user;