3

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
Improve this question

4 Answers 4

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you, is there a specific rule as to when to use subqueries? I am not super familiar with SQL but it appears those joins are in a sub query in the form statement right?
3

'''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;
Improve this answer

Comments

0

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
Improve this answer

Comments

0

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).

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.