sql - COUNT with a condition for a query with Group by and Join

Question

Requirements:

Table 1:

Id      Key          SiteId ItemId    OrderNumber
1       ABCD           X      BTL        NULL
2       BCDE           X      BTL        ABCD
3       CDEF           X      DDD        BSFE

Table 2:

Id  ItemId    Name
1    BTL      B Prd
2    DDD      D Prd

Results Required:

Id  ItemId      Name    AvailableKeys
1     BTL      B Prd      1
2     DDD      D Prd      0

Available Keys is calculated as the count of Items in table 1 having order numbers as null

Queries tried:

1) 
SELECT ps.ItemId, COUNT(ps.ItemId) AS AvailableKeys, item.Name
FROM TABLE1 AS ps 
LEFT JOIN TABLE2 as item ON item.ItemId = ps.ItemId 
WHERE ps.SiteId = 'X'
GROUP BY ps.ItemId, item.Name

   -- With this query, I am getting a count of the whole number of items but not the ones having order number as null (Count including even the Unavailable Items)

2) 
    SELECT ps.ItemId, COUNT(ps.ItemId) AS AvailableKeys, item.Name
    FROM TABLE1 AS ps 
    LEFT JOIN TABLE2 as item ON item.ItemId = ps.ItemId 
    WHERE ps.OrderNumber IS NULL AND ps.SiteId = 'X'
    GROUP BY ps.ItemId, item.Name

   -- With this query, I am missing the items having **ZERO** available keys

Can anyone help me out in building a query to get the required result set from data.

Thanks in advance.

question from:https://stackoverflow.com/questions/65892049/count-with-a-condition-for-a-query-with-group-by-and-join

Answer 1

You want a conditional sum as follows:

SUM(CASE WHEN OrderNumber IS NULL THEN 1 ELSE 0 END) AS AvailableKeys

And the complete query:

SELECT item.id, ps.ItemId
    , item.Name
    , SUM(CASE WHEN OrderNumber IS NULL THEN 1 ELSE 0 END) AS AvailableKeys
FROM TABLE1 AS ps 
LEFT JOIN TABLE2 as item ON item.ItemId = ps.ItemId 
WHERE ps.SiteId = 'X'
GROUP BY item.id, ps.ItemId, item.Name;

Returns:

id	ItemId	ItemName	AvailableKeys
1	BTL	B Prd	1
2	DDD	D Prd	0