CODEWITHSUNDEEP
mysqlsql
POV
POV

Reputation: 12015

SQL trick count number of rows by months?

I have an elementary SQL query:

SELECT MONTH(created_at), COUNT(id) as total FROM `clients` GROUP BY MONTH(created_at)

It returns me data groupped by month like as:

MONTH(created_at) | total
09                   1
10                   2

How to fill remaining months to zero? So, in result I need to get all months:

MONTH(created_at) | total
09                   1
..                   2
12                   5

I tried this way:

SELECT months.id, COUNT(clients.id) as total FROM `months` LEFT JOIN `clients` ON MONTH(created_at) = months.id GROUP BY MONTH(created_at)

Upvotes: 1

Views: 91

Answers (3)

POV
POV

Reputation: 12015

Thank all for answers, especially Alex, this way works:

SELECT months.id, COUNT(clients.id) as total FROM `months` LEFT JOIN `clients` ON  months.id = MONTH(created_at) GROUP BY months.id;

Upvotes: 1

Alex
Alex

Reputation: 741

The best practice is to have CALENDAR table, from which you can query period you need and then left join table with data. Or you can simply generate list of periods you need. In case you have a small period, you can use derived table. The fastest way in this case would be excel-generated list.

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use a derived table with all month numbers and left join your table on to that.

SELECT mths.mth, COUNT(c.id) as total
FROM (select 1 as mth union select 2 union select 3 union
      select 4 union select 5 union select 6 union select 7 union
      select 8 union select 9 union select 10 union select 11 union select 12) mths
LEFT JOIN `clients` c on mths.mth=month(c.created_at)
GROUP BY mths.mth

Upvotes: 2

Related Questions