CODEWITHSUNDEEP
mysqlcount
sbsatter
sbsatter

Reputation: 581

Count records for every month, including those with zero results

How do I count the number of results from MySql database for every month in the past year wherein there might not exist a record for a specific month but it would show zero as the count? For instance, I would like to count the number of registrations that occurred every month, and include those results too where no registrations occurred.

    Month    Count
      1        4
      2        2
      .        .
      .        .
      6        0
      .        .
      .        .

Upvotes: 1

Views: 951

Answers (1)

sbsatter
sbsatter

Reputation: 581

While although a lot of answers exist, I found most of them difficult to read and understand. However, using derived tables, I think it's easier to do so with the given query below, where we are trying to count the number of registrations for the past 12 months from user table:

select derived.mm as month, count(u.reg_date) as count from (
    SELECT 1 mm 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
) derived
left join user u
on derived.mm = month(reg_date) 
    and u.reg_date > LAST_DAY(DATE_SUB(curdate(),INTERVAL 1 YEAR))
group by derived.mm

HOW THIS WORKS

  • It derives a table (alias derived) which returns 12 rows, 1 for each of the 12 months, i.e. 1 to 12 [in the subquery; line 2].
  • It joins the month number of the registration date to the corresponding month number from the given derived table. [for months where no registrations occured, the joined reg_date result is null]
  • And clause is as usual, counting the previous 12 months [current month will be indexed at 12]
  • It groups the results by the original month index.

    • Hope this comes to help someone out.

    Upvotes: 2

    Related Questions