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

You can use CTE:

WITH tblSold AS (
    SELECT
      global.shopname,
      COUNT(stock.sold) AS sold
    FROM global
      INNER JOIN stock ON global.id = stock.shop
    GROUP BY shopname),
    tblReturned AS (
      SELECT
        global.shopname,
        COUNT(stock.returned) AS returned
      FROM global
        INNER JOIN stock ON global.id = stock.shop
      WHERE stock.datereturned > '1.01.2010'
      GROUP BY shopname)
SELECT
  shopname,
  sold,
  returned,
  sold - returned
FROM tblSold
  INNER JOIN tblReturned USING (shopname);

Answer 2

SELECT t1.shopname,
       t1.items_sold,
       t2.items_returned,(t1.items_sold-t2.items_returned) AS NET 
FROM (SELECT global.shopname, COUNT(stock.sold) as items_sold
      FROM global
      INNER JOIN  stock ON global.id = stock.shop
      GROUP BY shopname) t1
INNER JOIN  (SELECT global.shopname, COUNT(stock.returned) as items_returned
             FROM global
             INNER JOIN  stock ON global.id = stock.shop
             WHERE stock.datereturned > '2010-01-01'
             GROUP BY shopname) t2
ON t1.shopname = t2.shopname

Answer 3

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;