How to extract current stock from table in SQL Server 2000

Question

I'm making and application in vb.net using Visual Studio 2005 and SQL Server 2000. I have to keep track of the stock. The stock when added has its status set as "Unsold". When some stock is removed then the transaction keeps the status of stock as "Sold". The status can also be "Expired" for the stock that has been expired and when expired stock is claimed to the company then the status is "claimed"...Now from all this I want to extract the stock which is currently in hand. At a basic level with two status values i.e. "Sold" and "Unsold" I'm using the following query to extract the results from database but it doesn't calculate the difference sold and unsold items...

 select category, item_name, 
        sum(crtns_added) as Cartons, sum(pieces_added) as Pieces, 
        sum(total_price) 
 from Stock 
 where status = 'unsold' 
 group by category, item_name

I have also tried this

select category, item_name, (sum(crtns_added) - sum(crtns_removed)) as Cartons, 
       (sum(pieces_added)- sum(pieces_removed)) as Pieces, 
       sum(total_price) 
from Stock 
where status = 'unsold' or status = 'sold' 
group by category, item_name

Answer 1

You need to use two sub-selects to select the items with the appropriate values - something like:

SELECT
    category, 
    item_name, 
    (SELECT SUM(crtns_added) FROM dbo.Stock s1 WHERE s1.ItemID = s.ItemID AND s1.Status = 'unsold') 'Unsold Cartons',
    (SELECT SUM(crtns_added) FROM dbo.Stock s2 WHERE s2.ItemID = s.ItemID AND s2.Status = 'sold') 'Sold Cartons'
 FROM 
    dbo.Stock s
 GROUP BY 
    category, item_name

This assume there's an itemId column that uniquely identifies your items - some kind of a primary key. With this technique, you can get the sum of the sold or unsold cartons or pieces - whatever you need.