Reputation: 41
I have two tables below and I want to produce a third table with the sum of the value of imports and value of exports grouped by name.
table name: companies
country | name
canada a
usa b
china c
france d
table name: orders
id| importer | exporter | value
23 canada usa 10
24 usa china 50
25 canada china 10
26 france usa 40
I can produce the sum of imports and sum of exports tables seperately using a join for example:
sum of importers
select name, sum(value)
from orders
full outer join companies
on orders.importer = companies.country
I would like to see one table combining both sum of imports and sum of exports grouped by the name. The sum of imports and sum of exports is a sum on the value column in the orders table. I'm confused as to if i have to use a subquery here.
Example table
Name | sum of importer | sum of exporter
a 20 0
b 50 50
c 0 60
d 40 0
Upvotes: 3
Views: 4688
Reputation: 31
'''It's actually very simple.. use the following -
select c.name, IFNULL(sum(i.value),0) as sum_of_imports, IFNULL(sum(e.value),0) as sum_of_exports from companies c
Left join orders i on c.country = i.importer
Left join orders e on c.country = e.exporter
group by c.name
order by c.name asc;
Upvotes: 3
Reputation: 422
I'd solve it like this:
WITH importer AS (SELECT orders.importer, SUM(orders.value) AS t_import
FROM orders
GROUP BY 1),
exporter AS (SELECT orders.exporter, SUM(orders.value) AS t_export
FROM orders
GROUP BY 1)
SELECT c.name, COALESCE(i.t_import, 0) sum_of_import, COALESCE(e.t_export, 0) sum_of_export
FROM company c
LEFT JOIN importer i ON c.country = i.importer
LEFT JOIN exporter e ON c.country = e.exporter
Which gave me:
name | sum_of_import | sum_of_export
a 20 0
b 50 50
c 0 60
d 40 0
Planning Time: 0.157 ms
Execution Time: 0.120 ms
My thinking: I decided to split it into two different CTEs because it is easier to then join. You get the value for each country for when it's an importer or exporter. Then using a left join (to not lose any country that is not showing as an importer or exporter).
Upvotes: 0
Reputation: 13247
You need to use, two full outer join
separately and join them based on name
column for this purpose.
The working query is:
select g1.name, g1.sumofimporter, g2.sumofexporter
from (
select c.name
,COALESCE(sum(o1.value), 0) as sumofimporter
from companies c
full outer join orders o1 on o1.importer = c.country
group by c.name
) g1
join (
select c.name
,COALESCE(sum(o2.value), 0) as sumofexporter
from companies c
full outer join orders o2 on o2.exporter = c.country
group by c.name
) g2 on g2.name = g1.name
order by g1.name
Note: Since the post is about postgresql, but I created a working demo on SQL Server for testing purpose.
Please find the demo on db<>fiddle
Upvotes: 5
Reputation: 16918
Check this-
SELECT A.name,A.sum_of_importer,COALESCE(SUM(O2.value),0) sum_of_exporter
FROM
(
SELECT C.name,C.country,COALESCE(SUM(Value),0) AS sum_of_importer
FROM Companies C
LEFT JOIN orders O ON C.country = O.importer
GROUP BY C.name,C.country
)A
LEFT JOIN orders O2 ON A.country = O2.exporter
GROUP BY A.name,A.sum_of_importer
ORDER BY 1
Upvotes: 0