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
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: