How to Group by Day, Date, Hour, Month or Year in SQL Server

Grouping data by day, date, hour, month, or year in SQL Server involves using the GROUP BY clause and appropriate date functions to extract or manipulate the relevant portions of the datetime column.

The SQL Server GROUP BY operator is similar to the SQL GROUP BY Operator. The GROUP BY clause is a powerful feature for grouping rows based on unique combinations of values in specified columns. It facilitates the aggregation of data, allowing the application of aggregate functions to obtain meaningful insights.

Syntax:

SELECT column1, column2, aggregate_function(column3)

FROM table_name

GROUP BY column1, column2;

Explanation: In the above query, we used the GROUP BY clause to group rows in the result set based on unique combinations of values in columns column1 and column2. The SELECT clause then retrieves these grouped columns along with the result of applying an aggregate function aggregate_function(column3) to another column column3.

DATEPART()

SQL Server provides a range of date and time functions that enable the database to extract and manipulate date and time information. These functions enable us to perform various operations such as extracting components of a date, performing calculations between dates, and formatting date values.

Syntax:

SELECT DATEPART(unit, date_column) AS result

FROM table_name;

Explanation: Above query uses the DATEPART function to extract a specific time unit (e.g., year, month, day) from the date_column in the table_name, and the result is labeled as result in the output.

We have an #TempProductSales Table with some sample data. We use this table to understand GROUP BY clause with data and time functions.

Output:

#TempProductSales Table

GROUP BY Day

Query:

SELECT FORMAT(SaleDate, 'dddd') AS DayOfWeek,
       SUM(QuantitySold) AS TotalQuantitySold
FROM #TempProductSales
GROUP BY FORMAT(SaleDate, 'dddd');

Output:

Result using Group By Day of Week

Explanation: In above query, we use FORMAT function to extract the day of week, allowing us to group sales by Day of week. The SUM function calculates the total quantity sold for each day of week.

Grouping Data by Day, Date, Hour,Month or Year

GROUP BY Date

Query:

SELECT CAST(SaleDate AS DATE) AS SaleDay,
       SUM(QuantitySold) AS TotalQuantitySold
FROM #TempProductSales
GROUP BY CAST(SaleDate AS DATE);

Output:

Result using Group By Date

Explanation: In above query, we use the CAST function to extract the date part of the SaleDate, allowing us to group sales data by date. The SUM function calculates the total quantity sold for each day.

GROUP BY Month

Query:

SELECT DATENAME(MONTH, SaleDate) AS Month,
       SUM(QuantitySold) AS TotalQuantitySold
FROM #TempProductSales
GROUP BY DATENAME(MONTH, SaleDate);

Output:

Result using Group By Month

Explanation: In above query, we use the DATENAME function to extract the month name from the SaleDate, allowing us to group sales data by Month. The SUM function calculates the total quantity sold for each Month.

GROUP BY Year

Query:

SELECT YEAR(SaleDate) AS Year,
       SUM(QuantitySold) AS TotalQuantitySold
FROM #TempProductSales
GROUP BY YEAR(SaleDate);

Output:

Result using Group By Year

Explanation: In above query, we use YEAR function to extract the year from the SaleDate, allowing us to group sales data by Year. The SUM function calculates the total quantity sold for each Year.

GROUP BY Hour

Query:

SELECT FORMAT(SaleDate, 'HH') AS SaleHour,
       SUM(QuantitySold) AS TotalQuantitySold
FROM #TempProductSales
GROUP BY FORMAT(SaleDate, 'HH');

Output:

Result using Group By Hour

Explanation: In above query, we use the FORMAT function to extract the hour part from the SaleDate column in the format ‘HH’, which represents the two-digit hour (00 to 23). The SUM function calculates the total quantity sold for each unique hour.

Conclusion

The SQL Server GROUP BY clause, coupled with date and time functions, allows for versatile analysis and aggregation of temporal data. The choice of specific functions depends on the desired output and formatting preferences. Understanding these techniques is crucial for anyone dealing with time-based data in SQL Server databases.