Do NOT include DICTINCT in HAVING!
I see a lot of solution where you use DISTINCT in HAVING clause to filter out customers whose distinct product counts does not match the total unique product co
I see a lot of solution where you use DISTINCT in HAVING clause to filter out customers whose distinct product counts does not match the total unique product count. MySQL server let you pass but that is the logic bug of the compiler.\n**Wrong Solution:**\n```\nSELECT customer_id\nFROM Customer\nGROUP BY customer_id\nHAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);\n```\nThe funtamental flaw of such solution is that one should never include a DISTINCT within HAVING clause since HAVING only processes in terms of groups and has no idea about unique values within a certain group, LET ALONE you see that ```product_key``` is included in HAVING, which is does not exist in GROUP BY clause.\n\nPer SQL Documentation (https://www.ibm.com/docs/en/informix-servers/12.10?topic=statement-having-clause):\n```The condition in the HAVING clause cannot include a DISTINCT or UNIQUE aggregate expression```\n\nYou should NEVER:\n```\n1. include DISTINCT or UNIQUE in HAVING clause\n2. include attributes in HAVING clause that are not presented in GROUP BY clause\n```\n\n\n**Correct Solution:**\n```\n# get unique product counts\nWITH UniqueProductCount AS (\n    SELECT COUNT(*) AS ct\n    FROM Product\n), \n# get unique purchases from customers\nUniquePurchases AS (\n    SELECT DISTINCT C.customer_id, C.product_key\n    FROM Customer AS C\n), \n# get unique purchase counts for customers\nProductCount AS (\n    SELECT UP.customer_id, COUNT(*) AS ct\n    FROM UniquePurchases AS UP\n    GROUP BY UP.customer_id\n)\n\n# select customers whose unique purchased products equal to unique product counts\nSELECT PC.customer_id\nFROM ProductCount AS PC, UniqueProductCount AS UPC\nWHERE PC.ct = UPC.ct\n```\n
[]