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

id name product_count
1 category A 2
2 category B 3
3 category C 0
4 category D 1

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

id name product_count
1 category A 1
2 category B 2

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.

id name product_count
1 category A 1
2 category B 2
3 category C 0
4 category D 0
Write a review
Message:
Rating:
Name:
Email:
(optional)
(Your email that entered here will not show anywhere on website and will use only reference when someone reply your post)
  I want to receive a copy of email on this post (your email is needed)
 
Code: