Issue

This Content is from Stack Overflow. Question asked by oldsix

How to fix this code to show line items are supplied by suppliers in AFRICA for orders made by customers in UNITED STATES?

select Sum(Result.counts) from (
    SELECT count(l_orderkey)  AS counts 
    FROM lineitem
    INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
    INNER JOIN customer ON orders.o_custkey = customer.c_custkey
    INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
    WHERE n_name = "UNITED STATES" 
    union 
    SELECT count(l_orderkey) AS counts 
    FROM lineitem
    INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
    INNER JOIN supplier ON lineitem.l_suppkey = supplier.s_suppkey
    INNER JOIN nation ON supplier.s_nationkey = nation.n_nationkey
    INNER JOIN region ON nation.n_regionkey = region.r_regionkey
    WHERE r_name = "AFRICA"
    )Result;

Solution

You can run an UNION ALL

select Sum(Result.counts) from (
    SELECT count(l_orderkey)  AS counts 
    FROM lineitem
    INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
    INNER JOIN customer ON orders.o_custkey = customer.c_custkey
    INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
    WHERE n_name = 'UNITED STATES'
    union ALL
    SELECT count(l_orderkey) AS counts 
    FROM lineitem
    INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
    INNER JOIN supplier ON lineitem.l_suppkey = supplier.s_suppkey
    INNER JOIN nation ON supplier.s_nationkey = nation.n_nationkey
    INNER JOIN region ON nation.n_regionkey = region.r_regionkey
    WHERE r_name = 'AFRICA'
    )Result;

or make something to differentiate both lines

select Sum(Result.counts) from (
    SELECT count(l_orderkey)  AS counts , 'UNITED STATES' 
    FROM lineitem
    INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
    INNER JOIN customer ON orders.o_custkey = customer.c_custkey
    INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
    WHERE n_name = 'UNITED STATES' 
    union
    SELECT count(l_orderkey) AS counts , 'AFRICA'
    FROM lineitem
    INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
    INNER JOIN supplier ON lineitem.l_suppkey = supplier.s_suppkey
    INNER JOIN nation ON supplier.s_nationkey = nation.n_nationkey
    INNER JOIN region ON nation.n_regionkey = region.r_regionkey
    WHERE r_name = 'AFRICA'
    )Result;

Here is a simplified version to show that both work https://dbfiddle.uk/LE5oHz95

This Question was asked in StackOverflow by oldsix and Answered by nbk It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.