Count records for every month in a year

Checking data quality: Keeping an eye on anomalies

In order to ensure your aggregated data is accurate review it for completeness. Verify that the total count for the year is as expected and that your monthly counts appear plausible. Any oddities could indicate fundamental issues with your data or missing records.

No EXTRACT()? No problem!

If your DBMS does not support EXTRACT(), the YEAR() and MONTH() functions will suffice:

SELECT
  YEAR(date_column) AS Year,
  MONTH(date_column) AS Month,
  COUNT(*) AS Total
FROM
  table_name
WHERE
  YEAR(date_column) = 2012
GROUP BY
  Year, Month
ORDER BY
  Month;


Handling edge cases: Include everyone in the party

Performing regular aggregations could cause months with no records to not show up, and we want them at the party too! To include every month of the year with zero counts if necessary, follow this recipe:

1. The secret ingredient is to generate a series of months.
2. Cook this with your data table on a stove called JOIN, making sure each month is present.

WITH Months (Month) AS (
  SELECT 1 UNION ALL SELECT 2 UNION ALL ... UNION ALL SELECT 12
)
SELECT
  m.Month,
  COALESCE(t.Total, 0) AS Total 
FROM
  Months m
LEFT JOIN (
  SELECT
    MONTH(date_column) AS Month,
    COUNT(*) AS Total 
  FROM
    table_name
  WHERE
    YEAR(date_column) = 2012 -- Change the year to match your data's ‘vintage’
  GROUP BY
    Month
) t ON m.Month = t.Month;


Taking it a step further: Addressing different scenarios

Multi-year records and you

If your data spans multiple years but you need isolation of each year's month count, don't forget to add the year in your GROUP BY clause. Mixing counts from different years but same months is just as confusing as mixing mustard and chocolates!

Why CASE when you can SUM up?

Use CASE statements with SUM() nested in to provide individual columns for each month's count. If you're feeling lazy, put the months across -- it's less scrolling!

Choose your date range

The world isn't perfect, neither is date-based data. Your records could span an inconvenient range of dates -- not fitting neatly within calendar years. Use the BETWEEN operator to define your WHERE clause's date range:

WHERE date_column BETWEEN '2012-04-01' AND '2013-03-31'
