counting using join information

Question

I have two tables.

One table records orderstock which has FK_stock and FK_orderNo

I want to count the number of orders each item of stock has. The following code works correctly to do this:

(1)
    SELECT orderstock.FK_orderNo, Count(orderstock.FK_stock) AS CountOfFK_stock
     FROM stock INNER JOIN orderstock ON stock.StockID = orderstock.FK_stock 
    GROUP BY orderdemo.FK_orderNo 

However, I wish to add to this such that only stock items which are non perishable (stock.perishable=0) are listed. So something like

SELECT orderstock.FK_orderNo, Count(orderstock.FK_stock) AS CountOfFK_stock 
FROM stock INNER JOIN orderstock ON stock.stockID = orderstock.FK_stock 
WHERE stock.perishable=0 
GROUP BY orderstock.FK_orderNo 

How do I access information relating to the FK_stock to make this work? When I attempt to combine information from the stock table to this end, each item of stock is counted separately.

Results from (1)

 FK_OrderNo     CountOfFK_Stock     
 9      10
104     8
105     3
106     10
107     8
108     10
109     11
110     9

Desired results (something like):

 FK_OrderNo     CountOfFK_Stock     
 9      7
104     8
105     3
106     4
107     7
108     2
109     11
110     6 

Answer 1

I guess you are looking for conditional count

Move the where clause filter to Count Aggregate and make the count aggregate to count the record only when stock.perishable = 0.

SELECT orderdemo.fk_orderno, 
       Count(CASE 
               WHEN stock.perishable = 0 THEN 1 
             END) AS nonperishable_count 
FROM   stock 
       INNER JOIN orderdemo 
               ON stock.studentid = orderdemo.fk_stock 
GROUP  BY orderdemo.fk_orderno 

Count Aggregate can be replaced by SUM aggregate as well. Something like this

   Sum(CASE 
         WHEN stock.perishable = 0 THEN 1 
         ELSE 0 
       END) AS nonperishable_count