PHP & MySQL Using SUM and CASE in statement

As an alternative to general SQL statement, sometimes you can use built-in MySQL function to help in some complex statement. This article will demonstate of how to use SUM and CASE in the SQL statement. This will work like SQL Count() statement.

Normally SQL Count statement will look like this

SELECT c.id, c.name, count(p.id) as product_count
FROM product_categories c left join products p on c.id=p.category_id
GROUP BY c.id, c.name

The result should look like

The above SQL statement is still not sophisticate and work fine. But in some case you might need to filter some more like:

SELECT c.id, c.name, count(p.id) as product_count
FROM product_categories c left join products p on c.id=p.category_id
WHERE p.status=1
GROUP BY c.id, c.name

The result will change to

This is the wrong one because the product_count that is 0 is not include in the answer. That means the result other than p.status=1 will not show and you might not get the correct answer.

To correct this you can try using SUM and CASE of MySQL on the statement. So the statement should be:

SELECT c.id, c.name, 
    SUM(CASE WHEN p.status=1 THEN 1 ELSE 0 END) as product_count
FROM product_categories c left join products p on c.id=p.category_id

GROUP BY c.id, c.name

You will get the correct answer as below and this should do the trick.