Group by Month in SQL: A Comprehensive Guide with Examples in SQL Server

Last updated on February 11th, 2023 at 09:14 am

Grouping data by different time periods such as days, weeks, and months is one of the most important concepts in SQL. In this article, we will look at how to group data by month in SQL Server using examples.

What is SQL Group By Clause?

The GROUP BY clause in SQL Server is a clause used in SELECT statements to group rows based on one or more columns. The GROUP BY clause is used in conjunction with aggregate functions such as SUM, AVG, MIN, MAX, and COUNT, which allow you to perform calculations on grouped data.

For example, You can consider a table named Sales_Data, where each row represents a single sale. If you want to know the total sales for each month then you can use the GROUP BY clause to group the rows by the month, and then use the SUM aggregate function to calculate the total sales for each month.

The syntax for the group by month in SQL

The syntax for the group by month in SQL is as follows:

SELECT DATEPART(month, [column_name]) AS [Month], SUM([column_name]) AS [Total_Sales]
FROM [table_name]
GROUP BY DATEPART(month, [column_name])

In this example, the DATEPART function is used to extract the month from the date column, and the GROUP BY clause is used to group the data based on this month. The SUM aggregate function is used to calculate the total sales for each month.

You can replace the [column_name] and [table_name] with the actual names of your column and table. Additionally, you can change the DATEPART function to YEAR or DAY to group data by year or day instead of month.

Example 1: Group by Month in SQL Server

The following is an example that will help you understand “How to Group by Month in SQL” better. So let’s first create a table Sales_Data with some records.

-- Creating Sales_Data table
CREATE TABLE Sales_Data (
  Date date,
  Product_Name varchar(50),
  Sales int
);

-- Inserting data into Sales_Data table

INSERT INTO Sales_Data (Date, Product_Name, Sales)
VALUES 
  ('2022-01-01', 'Product 1', 100),
  ('2022-02-01', 'Product 2', 150),
  ('2022-03-01', 'Product 3', 200),
  ('2022-04-01', 'Product 4', 250),
  ('2022-05-01', 'Product 5', 300),
  ('2022-01-01', 'Product 2', 505),
  ('2022-02-01', 'Product 1', 600),
  ('2022-03-01', 'Product 3', 70),
  ('2022-04-01', 'Product 4', 800),
  ('2022-05-01', 'Product 5', 90),
  ('2022-06-01', 'Product 1', 110),
  ('2022-07-01', 'Product 2', 120),
  ('2022-08-01', 'Product 3', 150),
  ('2022-09-01', 'Product 4', 140),
  ('2022-10-01', 'Product 5', 150);

The output of the following SELECT query will be as follows.

SELECT * FROM Sales_Data;

You can use the following SQL statement to group the sales data by month and calculate the total quantity sold for each month.

-- Grouping data by month using Datepart function
SELECT 
  Datepart(month, Date) as Month,
  SUM(Sales) as Total_Sales
FROM Sales_Data
GROUP BY Datepart(month, Date);

The result after executing the above query statement is as follows.

Expected Output:

This example demonstrates how to group data by month in SQL Server using the GROUP BY clause and the DATEADD and DATEDIFF functions. By grouping data in this way, you can easily summarize and analyze data based on specific time periods.

Example 2: Group by Month in SQL Server

Let’s begin with a simple Furniture table that looks like this:

-- Creating Furniture table
CREATE TABLE Furniture (
   Id int,
  Product_Name varchar(50),
  Production_Time datetime,
);

-- Inserting data into Furniture table
INSERT INTO Furniture (Id, Product_Name, Production_Time)
VALUES  (1,'Chair','2022-10-01 10:45:25'),
        (2,'Lift chair','2022-10-01 10:45:25'),
		(3,'Bean bag chair','2022-02-05 11:00:00'),
		(4,'Chaise longue','2023-01-05 10:00:00'),
		(5,'Ottoman','2023-01-05 12:00:00'),
		(6,'Recliner','2023-01-05 12:00:00');

Let’s group records in a table based on both the month and year by using two DATEPART() functions in the SQL query.

 SELECT
  DATEPART(YEAR, Production_Time) AS year,
  DATEPART(MONTH, Production_Time) AS month,
  COUNT(Id) AS count
FROM furniture
GROUP BY
  DATEPART(MONTH, Production_Time),
  DATEPART(YEAR, Production_Time);

The following is the result of the above query:

In SQL Server, to categorize data by month, the DATEPART() function is used. This function extracts the specified component (such as year, month, etc.) from a given date. The MONTH and YEAR arguments are used twice each with the DATEPART() function to extract the month and year respectively from the production timestamp. The two calls to DATEPART() are then combined in the GROUP BY clause to group the furniture based on the production month and year.

In the SELECT clause, the same two calls to DATEPART() are utilized to display the year and month labels.

key points about the GROUP BY clause in SQL:

• The GROUP BY clause is used in SELECT statements to group rows based on one or more columns.
• The GROUP BY clause must be used in conjunction with aggregate functions, such as SUM, AVG, MIN, MAX, and COUNT, to perform calculations on grouped data.
• The GROUP BY clause allows you to summarize and analyze data in meaningful ways, such as by month, year, or day.
• The GROUP BY clause is used to group the data before it is returned to the user.
• The GROUP BY clause is widely supported in SQL and other databases such as SQL Server, Oracle, MySQL, and PostgreSQL.

FAQs

References-Stackoverflow-How to group by month

You might want to read this too:

• Having VS Where Clause in SQL
• Delete vs Truncate vs Drop in SQL Server
• UNION vs UNION ALL in SQL SERVER
• Different ways to delete duplicate rows in SQL Server
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• Stored Procedure in SQL Server With Examples
• Create, Alter, and Drop Database In SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples
• SQL pivot tables: Understanding pivot tables in SQL Server

Don’t keep this post to yourself, share it with your friends and let us know what you think in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024