Guillaume Guillaume - 1 month ago 6
MySQL Question

How to show all rows in a GROUP BY statement?

How do I get all the rows being shown in a GROUP BY statement ?

Here is the query :

SELECT year(feed_date) as date_year,
month(feed_date) as date_month,
count(*) as nb_item
FROM table
WHERE year(feed_date) = '2015' AND
feed_title LIKE '%word%' AND
source = '3'
GROUP BY date_year, date_month


Here is the output :

-----------------
| 2015 | 7 | 5 |
| 2015 | 9 | 2 |
| 2015 | 10 | 4 |
| 2015 | 11 | 2 |
-----------------


Here is the desired output :

-----------------
| 2015 | 1 | 0 |
| 2015 | 2 | 0 |
| 2015 | 3 | 0 |
| 2015 | 4 | 0 |
| 2015 | 5 | 0 |
| 2015 | 6 | 0 |
| 2015 | 7 | 5 |
| 2015 | 8 | 0 |
| 2015 | 9 | 2 |
| 2015 | 10 | 4 |
| 2015 | 11 | 2 |
| 2015 | 12 | 0 |
-----------------

Answer

You can join the selected data with a subquery that gets all the existing years and months from the table.

SELECT t1.date_year, t1.date_monthmonth, IFNULL(t2.nb_item, 0) AS nb_item
FROM (SELECT DISTINCT YEAR(feed_date) AS date_year, MONTH(feed_date) AS date_month 
      FROM table) AS t1
LEFT JOIN (
    SELECT year(feed_date) as date_year, 
           month(feed_date) as date_month, 
           count(*) as nb_item 
    FROM table 
    WHERE year(feed_date) = '2015' AND
          feed_title LIKE '%word%' AND
          source = '3'
    GROUP BY date_year, date_month) AS t2
ON t1.date_year = t2.date_year AND t1.date_month = t2.date_month
ORDER BY t1.date_year, t1.date_month