Get count of product with special feature at each store

[robert d via AccessMonster.com]

I need to get the count of a certain product (with custom features) at
different locations. I also need to know if that Count is zero.

I'm thinking that a self LEFT JOIN is required, but I can't seem to get it to
work.

SELECT A.STORE, Count(A.PRODUCT_ID) FROM [TABLEA] LEFT JOIN (SELECT
PRODUCT_ID
FROM TABLEA WHERE FEATURE = 2) As B ON B.PRODUCT_ID = A.PRODUCT_ID
WHERE B.PRODUCT_ID IS NULL
GROUP BY A.STORE

So, I need to get the count of the product at each store that has this custom
feature. I also need to know if that count at a specific store is zero. Right
now, I'm not getting all of the stores returned and the counts are incorrect.
The correct results need to include every store and the count of that
particular item (with the custom feature) even if that count for the store is
zero.

Thank you for helping.

 

[John Spencer]

Do you have a table of locations? Assuming not, then try (UNTESTED) code.

SELECT A.STORE, Count(B.PRODUCT_ID)
FROM [TABLEA] As A LEFT JOIN
(SELECT STORE, PRODUCT_ID
FROM TABLEA
WHERE FEATURE = 2) As B
ON A.Store = B.Store
GROUP BY A.STORE