I have three tables x, y, z that have similar columns like this and the other columns are different:
table x:
| id | user_id | month | year | total_price |
|---|---|---|---|---|
| 1 | 1 | January | 2023 | 100.00 |
| 2 | 1 | February | 2023 | 200.00 |
table y:
| id | user_id | month | year | total_price |
|---|---|---|---|---|
| 1 | 1 | March | 2023 | 100.00 |
| 2 | 2 | May | 2023 | 200.00 |
table z:
| id | user_id | month | year | total_price |
|---|---|---|---|---|
| 1 | 1 | March | 2023 | 100.00 |
| 2 | 2 | May | 2023 | 200.00 |
And also there is user table:
| user_id | last_name | last_name |
|---|---|---|
| 1 | John | Wick |
| 2 | Bill | Week |
I wrote query but I don’t know how to join years and months into one column in result:
select u.user_id, sum(x.total_price + y.total_price + z.total_price), x.month, y.month, z.month x.year, y.year, z.year
from user u
join x x on u.user_id = x.user_id join y y on u.user_id = y.user_id
join z z on u.user_id = z.user_id
group by u.user_id, x.month, y.month, z.month, x.year, y.year, z.year;
So, I want to achieve that to join all three tables into one and sum total price per user_id, month and year:
| user_id | month | year | total_price |
|---|---|---|---|
| 1 | January | 2023 | 100.00 |
| 1 | February | 2023 | 200.00 |
| 1 | March | 2023 | 200.00 |
| 2 | May | 2023 | 400.00 |
>Solution :
Join all the table and try this.
SELECT u.user_id,
month,
year,
SUM(total_price) AS total_price
FROM user u
JOIN (
SELECT user_id, month, year, total_price FROM x
UNION ALL
SELECT user_id, month, year, total_price FROM y
UNION ALL
SELECT user_id, month, year, total_price FROM z
) AS combined
ON u.user_id = combined.user_id
GROUP BY u.user_id, month, year
ORDER BY u.user_id, year, month;