CODEWITHSUNDEEP
sqlpostgresqljoingroup-byunion
David
David

Reputation: 23

Group by date for multiple tables

(I'm using postgres)

I've got two queries from different tables. Each is grouped by date. What is the best way to join them into one query (UNION or JOIN)? Thanks in advance.

Query №1:

SELECT to_char(date,'MM') as mon,
       extract(year from date) as yyyy,
       SUM("table_1"."user_money") AS user_sum,
       SUM("table_1"."system_money") AS system_sum
FROM "table_1"
GROUP BY 1,2
ORDER BY 2,1;

Result for Query №1:

 mon | yyyy |     user_sum     |   system_sum    
-----+------+------------------+-----------------
 11  | 2015 |        10        |       50        
 12  | 2015 |        20        |       60        
(2 rows)

Query №2:

SELECT to_char(created_at,'MM') as mon,
       extract(year from created_at) as yyyy,
       SUM("table_2"."amount") AS payments_sum
FROM "table_2"
GROUP BY 1,2
ORDER BY 2,1;

Result for Query №2:

 mon | yyyy | payments_sum
-----+------+--------------     
 10  | 2015 |     500      
 11  | 2015 |     600      
 12  | 2015 |     700      
 01  | 2016 |     800      
(4 rows)

Required result:

 mon | yyyy | payments_sum |     user_sum     |   system_sum   
-----+------+--------------+------------------+----------------
 10  | 2015 |     500      |                  |                
 11  | 2015 |     600      |        10        |       50       
 12  | 2015 |     700      |        20        |       60      
 01  | 2016 |     800      |                  |                
(4 rows)

Upvotes: 2

Views: 1919

Answers (3)

user5780762
user5780762

Reputation:

Try this.

SELECT 
    to_char(date,'MM') as mon,
    extract(year from date) as yyyy,
    SUM("table_2"."amount") AS payments_sum,
    SUM("table_1"."user_money") AS user_sum,
    SUM("table_1"."system_money") AS system_sum
FROM 
    "table_1"
LEFT JOIN 
    "table_2" ON (to_char("table_1"."date", 'MM') = to_char("table_2"."date", 'MM') AND extract(year from "table_1"."date") = extract(year from "table_2"."date"))
GROUP BY 1,2
ORDER BY 2,1;

Upvotes: 0

MyBrainHurts
MyBrainHurts

Reputation: 2630

Here is another one in a more PostgreSQL specific syntax:

WITH query_one AS (
    SELECT 
        to_char(date,'MM') as mon,
        extract(year from date) as yyyy,
        SUM("table_1"."user_money") AS user_sum,
        SUM("table_1"."system_money") AS system_sum
    FROM 
        "table_1"
    GROUP BY 
        1,2
), query_two AS (
    SELECT 
        to_char(created_at,'MM') as mon,
        extract(year from created_at) as yyyy,
        SUM("table_2"."amount") AS payments_sum
    FROM 
        "table_2"
    GROUP BY 
        1,2
)
SELECT
    *
FROM
    query_one
    LEFT JOIN query_two USING (mon, yyyy)
ORDER BY 
    yyyy
    , mon;

The USING clause is easier to use and prevents mon and yyyy to be twice in the output and the rest is just for the sake of a clear view.

Upvotes: 1

Stidgeon
Stidgeon

Reputation: 2723

I think you can treat each of your queries as a subquery and then combine with a full outer join. Union probably isn't what you want here.

Something like this:

SELECT * FROM
(SELECT to_char(date,'MM') as mon,
   extract(year from date) as yyyy,
   SUM("table_1"."user_money") AS user_sum,
   SUM("table_1"."system_money") AS system_sum
FROM "table_1"
GROUP BY 1,2) AS q1
FULL OUTER JOIN 
(SELECT to_char(created_at,'MM') as mon,
   extract(year from created_at) as yyyy,
   SUM("table_2"."amount") AS payments_sum
FROM "table_2"
GROUP BY 1,2) AS q2
ON q1.mon = q2.mon AND q1.yyyy = q2.yyyy
ORDER BY 2,1

joining where the mon and yyyy values match.

Upvotes: 0

Related Questions