Re: COUNT with Case and Group by

[Ali Naeini]

Thank you for the fast answer.

The result by this query

SELECT COUNT(CASE WHEN `categories_id` = '262' THEN 1 END) AS `262`, COUNT(CASE WHEN `categories_id` = '155' THEN 1 END) AS `155` , COUNT(CASE WHEN `categories_id` = '134' THEN 1 END) AS `134` FROM products p INNER JOIN products_to_categories p2c1 on p2c1.products_id = p.products_id LEFT JOIN teppichfarbe tep on tep.teppichnr = p.products_id WHERE p.products_status = 1 AND p.long > 164 AND p.long < 382 AND p.width > 77 AND p.width < 282 AND ( tep.farbe = 'black1' OR tep.farbe = 'black2' OR tep.farbe = 'black3' )

is:

262 => 9
155 => 99
134 => 0

But for example count of products in category 262 should be 6 and not 9.
If i try this:

SELECT p.products_id FROM products p INNER JOIN products_to_categories p2c1 on p2c1.products_id = p.products_id LEFT JOIN teppichfarbe tep on tep.teppichnr = p.products_id WHERE p.products_status = 1 AND p.long > 164 AND p.long < 382 AND p.width > 77 AND p.width < 282 AND ( tep.farbe = 'black1' OR tep.farbe = 'black2' OR tep.farbe = 'black3' ) AND `categories_id` = '262'

i get this result:

products_id
23258
23258
23261
23261
26021
27613
27613
27620
34838

You can see some duplicates by the result. It should be only 6 unique products_id .

I know i can user by this query GROUPY BY products_id and will get the right result:

SELECT p.products_id FROM products p INNER JOIN products_to_categories p2c1 on p2c1.products_id = p.products_id LEFT JOIN teppichfarbe tep on tep.teppichnr = p.products_id WHERE p.products_status = 1 AND p.long > 164 AND p.long < 382 AND p.width > 77 AND p.width < 282 AND ( tep.farbe = 'black1' OR tep.farbe = 'black2' OR tep.farbe = 'black3' ) AND `categories_id` = '262' GROUP BY p.`products_id`

products_id
23258
23261
26021
27613
27620
34838

If i try to GROUPY BY mmy count query:

SELECT COUNT(CASE WHEN `categories_id` = '262' THEN 1 END) AS `262`, COUNT(CASE WHEN `categories_id` = '155' THEN 1 END) AS `155` , COUNT(CASE WHEN `categories_id` = '134' THEN 1 END) AS `134` FROM products p INNER JOIN products_to_categories p2c1 on p2c1.products_id = p.products_id LEFT JOIN teppichfarbe tep on tep.teppichnr = p.products_id WHERE p.products_status = 1 AND p.long > 164 AND p.long < 382 AND p.width > 77 AND p.width < 282 AND ( tep.farbe = 'black1' OR tep.farbe = 'black2' OR tep.farbe = 'black3' ) GROUP BY p.products_id, `categories_id`

Then i get this result:

262 | 155 | 134
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
......

What should i do?
I hope i could explain my problem good enough. Sorry for my bad english.

Thank you
Ali