CODEWITHSUNDEEP
sqlpostgresql
Imran W
Imran W

Reputation: 41

Combining the results of two SQL queries into one table but not with a union

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

Answers (4)

Achilles
Achilles

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

ShayHa
ShayHa

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

Arulkumar
Arulkumar

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

mkRabbani
mkRabbani

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

Related Questions