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?
1条答案
按热度按时间toiithl61#
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.Result :
Demo here