Need the difference between two columns and if the second has empty item, it displays the number in the first column

Question

I need the difference between items i bought and items i sold, items i bought in this table

  ID   |ItemName| PriceOfUnit  | NumberOfItems I bought |DateIBought| 
  1    |  tea   |       3      |            6           |15/11/2015 |
  2    | coffee |       5      |            4           |16/11/2015 |
  3    |  tea   |       4      |            10          |20/12/2015 |
  4    | juice  |       5      |            15          | 1/1/2016  |
  5    | coffee |       3      |            5           | 15/3/2016 |
  6    | water  |       5      |            2           | 16/4/2016 |

and items I sold is in this table

  ID   |ItemName| PriceOfUnit  | NumberOfItems I sold   |DateIBought| 
  1    | coffee |       5      |            6           |  1/1/2016 |
  2    |  tea   |       5      |            9           | 15/3/2016 |
  3    | coffee |       4      |            2           | 20/4/2016 |
  4    | juice  |       5      |            11          |  1/1/2016 |

I need a query , SQL query OR union Query in MS Access to get this result:

  ID   |ItemName| NumberOfItems I have   |
  1    | coffee |            1           |
  2    |  tea   |            7           |
  3    | juice  |            4           |
  4    |  water |            2           |

Where NumberOfItems I have = NumberOfItems I bought - NumberOfItems I sold

i tried this

q1:

SELECT ItemName, SUM(bought) as SumBought 
FROM tBought GROUP BY ItemName

q2:

SELECT ItemName, SUM(sold) as SumSold 
FROM tSold GROUP BY ItemName

q3:

SELECT q1.ItemName, (SumBought - SumSold) as difference 
FROM q1 inner join q2 on q1.ItemName = q2.ItemName

and i get this result

  ID   |ItemName| NumberOfItems I have   |
  1    | coffee |            1           |
  2    |  tea   |            7           |
  3    | juice  |            4           |

all i need now .. is to display NumberOfItems I boughtif i didn't sold any thing of this item
as this example was " water "

I don't understand the id column. If it's meaningless, get rid of it. Also, I would do this as a single table of transactions. — Strawberry, Jan 05 '16 at 14:27
You are nearly there. Look into [OUTER JOINS](https://msdn.microsoft.com/en-us/library/bb208894(v=office.12).aspx). At present your INNER JOIN only returns items purchased with 1 or more entries in the sold table. — David Rushton, Jan 05 '16 at 14:32

score -1 · Accepted Answer · answered Jan 05 '16 at 14:32

-1

I think you can change your last query to, to get the items in Q1 (Items Bought) even if none of them were sold.

SELECT q1.ItemName
     , (q1.SumBought - ISNULL(q2.SumSold, 0)) as difference 
  from q1 
  LEFT 
 OUTER
  join q2 
    on q1.ItemName = q2.ItemName

Hope this helps.

answered Jan 05 '16 at 14:32

vmachan

1,672
1
10
10

@ahmedsaber This definitely doesn't 'work' - at least not according to your specification. – Strawberry Jan 06 '16 at 17:28

score -1 · Answer 2 · edited May 23 '17 at 12:07

Above your existing approach, you can simply change your 3rd query to do a outer join instead of inner join:

SELECT q1.ItemName, 
       (ISNULL(SumBought, 0) - ISNULL(SumSold, 0)) as difference 
FROM q1 LEFT JOIN q2 on q1.ItemName = q2.ItemName

Just to stick everything into a single transaction and also better optimize execution plan, I will suggest to use CTE

;WITH q1 AS (
  SELECT ItemName, SUM(bought) as SumBought 
  FROM tBought GROUP BY ItemName ),
q2 AS (
  SELECT ItemName, SUM(sold) as SumSold 
  FROM tSold GROUP BY ItemName)
SELECT q1.ItemName, 
       (ISNULL(SumBought, 0) - ISNULL(SumSold, 0)) as difference 
FROM q1 LEFT JOIN q2 on q1.ItemName = q2.ItemName

Need the difference between two columns and if the second has empty item, it displays the number in the first column

2 Answers2