niksrb niksrb - 6 months ago 32
SQL Question

SQL constraints in WHERE clause?

I want to select all shops that have at least three products in their offer which costs between 5 and 20 and I need to show shop id and name and also number of products that satisfy the condition. This is the code:

SELECT SHOP.SHOP_ID, SHOP.SHOP_NAME, COUNT(PRODUCT.PRODUCT_ID) AS NUM
FROM SHOP JOIN PRODUCT USING (SHOP_ID)
GROUP BY SHOP_ID
HAVING (SELECT SHOP_ID FROM PRODUCT WHERE COST IS BETWEEN 5 AND 20 AND NUM>=3)


Problem I have is that constraint for NUM>3 is working, but in the result I am getting both, products with price under the 5 and over 20. It's like there is no part with BETWEEN and I am not getting why is that.

Answer Source

Do the filtering on price before the aggregation:

SELECT SHOP_ID, s.SHOP_NAME, COUNT(*) AS NUM
FROM SHOP s JOIN
     PRODUCT p
     USING (SHOP_ID)
WHERE p.COST IS BETWEEN 5 AND 20
GROUP BY SHOP_ID, SHOP_NAME
HAVING COUNT(*) >= 3;

Most, but not all, databases also allow you to write HAVING NUM > 3. Similarly, it is a good practice to include all unaggregated columns in the GROUP BY -- unless you really, really know what you are doing.