CODEWITHSUNDEEP
mysql
Guillaume
Guillaume

Reputation: 352

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

Upvotes: 0

Views: 85

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31792

You need a table containing all the year-month combinations for a LEFT JOIN. You can create it on the fly by cross joining all years and months:

SELECT y.date_year, m.date_month, count(*) as nb_item 
FROM (
    SELECT 1 as date_month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12
) m
CROSS JOIN (
    SELECT 2015 as date_year
) y
LEFT JOIN `table` t 
    ON  year(t.feed_date)  = y.date_year
    AND month(t.feed_date) = m.date_month
    AND t.feed_title LIKE '%word%'
    AND t.source = '3'
GROUP BY y.date_year, m.date_month

If you have a helper table with sequence numbers you can shorten the query to:

SELECT y.seq as date_year, m.seq as date_month, count(*) as nb_item 
FROM sequences y
CROSS JOIN sequences m
LEFT JOIN `table` t 
    ON  year(t.feed_date)  = y.date_year
    AND month(t.feed_date) = m.date_month
    AND t.feed_title LIKE '%word%'
    AND t.source = '3'
WHERE y.seq IN (2015)
  AND m.seq <= 12
GROUP BY y.seq, m.seq

Upvotes: 1

Barmar
Barmar

Reputation: 780949

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

Upvotes: 1

num8er
num8er

Reputation: 19372

by tag mysqli I can assume that You're using PHP.

So it can be done this way:

 $year = '2015';
 $data = [];
 foreach(range(1, 12) AS $month) {
   $data[$month] = [
     'date_year'  => $year,
     'date_month' => $month,
     'nb_item'    => 0
   ];
 }

 $q = "SELECT 
       year(feed_date) as date_year, 
       month(feed_date) as date_month, 
       count(*) as nb_item 
       FROM table 
       WHERE year(feed_date) = '".$year."'
       GROUP BY date_year, date_month";
 $q = mysqli_query($q);
 while($record = mysqli_fetch_assoc($q)) {
   $data[$record['date_month']]['nb_item'] = $record['nb_item'];
 }

 $data = array_values($data);
 print_r($data);

or with mysql it will be huge query:

SELECT 
  year(table.feed_date) AS date_year, 
  month(table.feed_date) AS date_month, 
  COALESCE(count(*), 0) as nb_item 
FROM (
  SELECT 1 as month 
  UNION ALL SELECT 2 
  UNION ALL SELECT 3 
  UNION ALL SELECT 4 
  UNION ALL SELECT 5 
  UNION ALL SELECT 6 
  UNION ALL SELECT 7 
  UNION ALL SELECT 8 
  UNION ALL SELECT 9 
  UNION ALL SELECT 10 
  UNION ALL SELECT 11 
  UNION ALL SELECT 12
) months 
LEFT JOIN table ON (months.month = month(table.feed_date))
WHERE year(table.feed_date) = '2015' 
GROUP BY date_year, date_month;

Upvotes: 2

Related Questions