Merging columns from different tables based on its values of FULL JOIN result

Question

I have two tables both with three columns, both have a week number and product category, and one has the incoming amount and the other has the outgoing amount of said product category per week. I'm looking to join these two tables such that I obtain a table with the incoming and outgoing amounts per product category per week.

The data is looking something like:

week_number	product_category	incoming_amount
1	cat1	5
4	cat2	6
4	cat2	2
4	cat3	6
11	cat1	6
11	cat3	4

week_number	product_category	outgoing_amount
2	cat1	5
3	cat2	6
4	cat2	1
4	cat2	7
15	cat1	6
15	cat1	4

When I join these two tables and group the columns to sum the incoming and outgoing amounts with the following code I get the below table as result.

SELECT i.week_number 
      ,i.product_category 
      ,o.week_number 
      ,o.product_category 
      ,SUM(i.incoming_amount ) AS sum_incoming_amount 
      ,SUM(o.outgoing_amount ) AS sum_outgoing_amount 
FROM incoming AS i
FULL OUTER JOIN outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category 
GROUP BY i.product_category, i.week_number, o.product_category, o.week_number;

week_number	product_category	week_number	product_category	incoming_amount	outgoing_amount
1	cat1	NULL	NULL	5	NULL
NULL	NULL	2	cat1	NULL	5
NULL	NULL	3	cat2	NULL	6
4	cat2	4	cat2	8	8
4	cat3	NULL	NULL	6	NULL
11	cat1	NULL	NULL	6	NULL
11	cat3	NULL	NULL	4	NULL
NULL	NULL	15	cat1	NULL	10

In the output I'm trying to achieve the week number and product category columns are merged as follows:

week_number	product_category	incoming_amount	outgoing_amount
1	cat1	5	NULL
2	cat1	NULL	5
3	cat2	NULL	6
4	cat2	8	8
4	cat3	6	NULL
11	cat1	6	NULL
11	cat3	4	NULL
15	cat1	NULL	10

How can I achieve this?

Answer 1

You need to calculate incoming/outgoing sums separately, then apply FULL OUTER JOIN :

COALESCE function used to select the first non-NULL value from a list of columns.

WITH cte_incoming AS (
  SELECT week_number, product_category, SUM(incoming_amount) AS sum_incoming_amount
  FROM incoming
  GROUP BY week_number, product_category
),
cte_outgoing AS (
  SELECT week_number, product_category, SUM(outgoing_amount) AS sum_outgoing_amount
  FROM outgoing
  GROUP BY week_number, product_category
)
SELECT COALESCE(i.week_number, o.week_number) AS week_number,
         COALESCE(i.product_category, o.product_category) AS product_category,
         sum_incoming_amount,
         sum_outgoing_amount
FROM cte_incoming AS i
FULL OUTER JOIN cte_outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category

Result :

week_number product_category    sum_incoming_amount sum_outgoing_amount
1           cat1                5                   null
2           cat1                null                5
3           cat2                null                6
4           cat2                8                   8
4           cat3                6                   null
11          cat1                6                   null
11          cat3                4                   null
15          cat1                null                10

Demo here