CODEWITHSUNDEEP
sqlpostgresql
Pioter
Pioter

Reputation: 13

SQL group three columns from three tables into one column

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

Upvotes: 1

Views: 65

Answers (3)

BugHunter
BugHunter

Reputation: 343

Combine rows from your tables and then perfom aggregation here is an example:

SQL

SELECT user_id, month, year, SUM(total_price) AS total_price
    FROM (
        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 CombinedData
    GROUP BY user_id, month, year
    ORDER BY user_id, year, month;

enter image description here

Upvotes: 0

M N
M N

Reputation: 96

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;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

You want a union based approach followed by an aggregation:

SELECT user_id, month, year, SUM(total_price) AS total_price
FROM
(
    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
) t
GROUP BY user_id, month, year
ORDER BY user_id;

Upvotes: 0

Related Questions