How to do I sum these postgresql two tables and group by week and year?-CodePudding

I am trying to sum two tables sales waste with results grouped by week and year. If I run each section by itself I get accurate results, but when I try my current query I get an error of "[42601] ERROR: subquery must return only one column"

This is what I currently have...

SELECT
(
    SELECT date_part('week', w."createdAt"),
        SUM("Quantity")
    FROM waste as w
     WHERE date_trunc('year', w."createdAt"::timestamptz at time zone 'America/Denver') =
           date_trunc('year', current_date - interval '1 year')
    group by 1
)
     
(
    SELECT date_part('week', sh.”createdAt"),
           SUM(quantity)
    FROM sales_hourly as sh
    WHERE date_trunc('year', sh."createdAt"::timestamptz at time zone 'America/Denver') =
          date_trunc('year', current_date - interval '1 year')
    group by 1
) AS weekly_total

CodePudding user response：

You can join both tables, but as both tables could have different weeks, you need a FULL OUTER JOIN which will cost time.

If it is guaranteed that both SELECTs have the same weeks , a INNER JOIN will suffice.

CREATE tABLE waste("createdAt" timestamp  , "Quantity" integer )

CREATE tABLE sales_hourly("createdAt" timestamp  , "Quantity" integer)

SELECT t1.week_t1,COALESCE(sum_t1,0) sum_waste,t2.week_t2,  COALESCE(sum_t2,0) sum_slaes
FROM
(
    SELECT date_part('week', w."createdAt") week_t1,
        SUM("Quantity") sum_t1
    FROM waste as w
     WHERE date_trunc('year', w."createdAt"::timestamptz at time zone 'America/Denver') =
           date_trunc('year', current_date - interval '1 year')
    group by 1
) t1
    FULL OUTER JOIN 
(
    SELECT date_part('week', sh."createdAt") week_t2,
        SUM("Quantity") sum_t2
    FROM sales_hourly as sh
     WHERE date_trunc('year', sh."createdAt"::timestamptz at time zone 'America/Denver') =
           date_trunc('year', current_date - interval '1 year')
    group by 1) t2 ON t1.week_t1 = t2.week_t2

week_t1 | sum_waste | week_t2 | sum_slaes
:------ | --------: | :------ | --------:

db<>fiddle here