Question 269055 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search