SQL: Performing a calculation on columns from two select statements in one query

Question

I want to select information from two unrelated SQL tables (formed by separate SQL queries) to give me one table with four columns: one as an identifier, one for values from the first table, one for values from the second table, and one for a calculation between them.

A simplified example is:

tblSold

 Shop name | items sold
 Shop A    | 100   
 Shop B    | 50    
 Shop C    | 75    
 Shop D    | 80    

tblReturned

  Shop name | Items returned
  Shop A    | 10
  Shop B    | 5   
  Shop C    | 7 
  Shop D    | 8   

And i'm trying to get a table that looks like this

  Shop name | items sold | items returned | net (sold - returned)
  Shop A    | 100        | 10             | 90
  Shop B    | 50         | 5              | 45
  Shop C    | 75         | 7              | 68
  Shop D    | 80         | 8              | 72

In the above case, tblSold comes from a query that looks like this:

SELECT global.shopname, COUNT(stock.sold) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
GROUP BY shopname

And tblReturned comes from a query that looks like this:

SELECT global.shopname, COUNT(stock.returned) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
WHERE stock.datereturned > 1.01.2010
GROUP BY shopname

From this previous question SQL: Two select statements in one query I got the following query:

(SELECT global.shopname, COUNT(stock.sold) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
GROUP BY shopname)

UNION

(SELECT global.shopname, COUNT(stock.returned) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
WHERE stock.datereturned > 1.01.2010
GROUP BY shopname)

Which got me a table with only two columns, and no way to make the calculation. Is there a way to join these in some manner to make it work?

Answer 1

I think you just need conditional aggregation:

SELECT g.shopname, COUNT(s.sold),
       SUM(CASE WHEN s.datereturned > '2010-01-01' AND s.returned IS NOT NULL THEN 1 ELSE 0 END) as returned,
       (COUNT(s.sold) - 
        SUM(CASE WHEN s.datereturned > '2010-01-01' AND s.returned IS NOT NULL THEN 1 ELSE 0 END)
       ) as net
FROM global INNER JOIN
     stock
     ON g.id = s.shop
GROUP BY g.shopname;

Having the date logic only apply to returns is strange. Also, I am guessing, though, that you want SUM() of the inventory and not COUNT(). So this may produce more accurate results:

SELECT g.shopname, SUM(s.sold) as items_sold,
       SUM(CASE WHEN s.datereturned > '2010-01-01' THEN s.returned ELSE 0 END) as items_returned,
       SUM(CASE WHEN s.datereturned > '2010-01-01' THEN s.items_sold - s.returned
                ELSE s.items_sold END)
FROM global INNER JOIN
     stock
     ON g.id = s.shop
GROUP BY g.shopname;