How to group by month from Date field using SQL

sql

date-formatting

sql-server

group-by

byAlex Kataev·Dec 13, 2024

To group by month, you need to use database-specific functions that extract the year and month from your date column. For MySQL, use MONTH() and YEAR(). In SQL Server, go for DATEPART(). Here are basic examples for each:

MySQL - a server often associated with sunny beaches and margaritas 🙂:

SELECT YEAR(your_date), MONTH(your_date), COUNT(*) //counts coconuts... I mean records...
FROM your_table //don't forget your beach... um, I meant table name 
GROUP BY YEAR(your_date), MONTH(your_date);

SQL Server - the reliable workhorse that gets things done too:

SELECT DATEPART(year, your_date), DATEPART(month, your_date), COUNT(*) //counting sheep... No, records!
FROM your_table 
GROUP BY DATEPART(year, your_date), DATEPART(month, your_date);

These chunky pieces of code aggregate rows per month and spit out a monthly grouped count. FYI, this happens without bothering about date formatting. Smooth sailing, isn't it? Off to the deep dive we go!

Get 'YYYY-MM' format with FORMAT()

For those who prefer their dates dainty and well-dressed in the 'YYYY-MM' format, the FORMAT() function comes in handy. However, note that it doesn't take a one-size-fits-all approach:

SQL Server:

SELECT FORMAT(your_date, 'yyyy-MM') as MonthYear, COUNT(*) 
FROM your_table 
WHERE your_date IS NOT NULL
GROUP BY FORMAT(your_date, 'yyyy-MM')
ORDER BY MonthYear;

What it does is, it outputs shapely results like '2023-01', '2023-02', etc.

No mixing up of years, please!

Ever mixed up salt with sugar in your coffee? Yeah, not a great start to the day. The same philosophy applies to month-over-month data! Always include the year in your GROUP BY clause⚠️:

SELECT DATEADD(month, DATEDIFF(month, 0, your_date), 0) as MonthStart, COUNT(*)
FROM your_table
WHERE your_date >= '2022-01-01' AND your_date <= '2023-12-31'
GROUP BY DATEADD(month, DATEDIFF(month, 0, your_date), 0)
ORDER BY MonthStart;

This ensures January 2022 and January 2023 don't become roommates and give you skewed results.

First-of-month grouping: because, why not?

Doing a roundup on the first-of-each-month can bring joy (and uniform timestamps) to your date groups. Make the most of DATEADD and DATEDIFF to achieve this:

SELECT DATEADD(month, DATEDIFF(month, 0, your_date), 0) as FirstOfMonth, COUNT(*)
FROM your_table
GROUP BY DATEADD(month, DATEDIFF(month, 0, your_date), 0);

Voila! All entries are now neatly sorted by their respective month's first day.

Counting like it matters

Counting specific occurrences, like how many times you binged on pizza last month, is a breeze. Use COUNT() with a condition like so:

SELECT DATEPART(year, your_date) as Year, DATEPART(month, your_date) as Month, COUNT(Status) as TotalCount //summoning the pizza counter...
FROM your_table
WHERE Status = 'Closed'
GROUP BY DATEPART(year, your_date), DATEPART(month, your_date);

Remember, Status and 'Closed' are placeholders for your real world column name and value.

Pitfall avoidance 101

Just like your nutritional needs don't include a Milky Way chocolate bar (okay, maybe once in a while doesn't hurt), your counts also don't need null values. Weed them out with a WHERE clause:

SELECT ...
FROM your_table
WHERE your_date IS NOT NULL
...

Next, don't mess up the column names and table names. Ensure they are accurate and dates are hitting the right notes in the 'YYYY-MM-DD' format. Say goodbye to unexpected errors and hello to smooth SQL sailing!

Custom Periods and Calendar Quirks

When your business doesn't play by the Gregorian calendar, you would need a calendar table or lookup table to do monthly grouping by fiscal months or other custom periods.

The TimeZone Tango

Dealing with time zones can be sometimes more annoying than dealing with your cats at 3 AM! Normalize usage to UTC or a consistent standard to dodge date grouping mix-ups.

explain-codes / Sql / How to group by month from Date field using SQL

Linked

Extract Month and Year from SQL DATE



Returning Month Name in SQL Server Query



Convert date to YYYYMM format



Count records for every month in a year



Sql grouping by month and year

