If you have made ​​reporting queries to extract statistics from large tables, you often had to take you into multiple queries to get to be all of your statistics, which can quickly become tedious.

Imagine the following two tables:

• news with a status published enum ('true','false) and a FK to category
• category

If you want to get the total number of news items in each category, of course the group by will help you:

SELECT
||t||n.category_id,
||t||COUNT(*) AS total_news
FROM 
||t||news n
||t||LEFT JOIN category c ON n.category_id = c.category_id
GROUP BY
||t||n.category_id
ORDER BY
||t||total_news DESC

This will give you all the category and the number of news items in each category.

Except that this is where it gets difficult, you would like to know at the same time, among the news of these category, how many news are published vs are unpublished.

Instinctively, you want to add count() with WHERE except that MySQL does not put clauses in the count() function.

You will resolve to make more requests and this is where we come in offering this solution:

SELECT
||t||n.category_id,
||t||COUNT(*) AS total_news,
||t||SUM(IF(n.news_published = 'true', 1, 0)) AS total_news_published,
||t||SUM(IF(n.news_published = 'false', 1, 0)) AS total_news_not_published
FROM 
||t||news n
||t||LEFT JOIN category c ON n.category_id = c.category_id
GROUP BY
||t||n.category_id
ORDER BY
||t||total_news DESC

The solution is to transform our utopian count where to a conditional sum which we affect directly the sum by making our test inside and forcing the +0 or +1.

The result is very consistent with what we wanted: