top of page

SQL GROUP BY to Summarize and Rollup Query Results


What Is “Grouping” In SQL, And Why Is It Needed?

Grouping in SQL group together is a powerful concept that allows us to group rows of data into one group, or more combinations and summarize the numeric values contained therein. By grouping data, we can perform aggregate functions like COUNT, SUM, AVG and MAX on the grouped fields. Grouping helps us organize our data into specific groups or classes according to our criteria for analysis. For example, if you want to know what class has the highest average score, you can use grouping by class_id in order to calculate this statistic.


SQL Query Execution Order

Here is a summary of the typical execution order of a SQL query:

FROM clause:

The FROM clause is executed first and identifies the tables or views from which the data will be retrieved.


JOIN clause:

If there are any JOIN clauses, they are executed next, joining multiple tables together based on a specified condition.


WHERE clause:

The WHERE clause is executed next, filtering the data based on the specified condition.


GROUP BY clause:

If there is a GROUP BY clause, the column by data in following table is grouped based on the specified columns.


HAVING clause:

The HAVING clause is executed next, filtering the groups based on the specified condition.


SELECT clause:

The SELECT clause is executed next, selecting the columns that will be displayed in the final query result for the following query set.


DISTINCT clause:

If there is a DISTINCT clause, duplicates are removed from same results in the same value of the result set.


ORDER BY clause:

If there is an ORDER BY clause, the data is sorted based on the specified columns.

LIMIT clause: If there is a LIMIT clause, the maximum number of rows returned is limited to the specified number two columns.


SQL Server GROUP BY And Different Versions Of SQL Server

The GROUP BY and clause syntax in SQL Server has evolved over different versions of the software. Here are some notable changes:


SQL Server 2000:

GROUP BY was introduced in SQL Server 2000.

It allowed you to group by expressions or column numbers, but not by column names or aliases.

It did not support the GROUPING SETS, CUBE or ROLLUP operators.

It supported aggregate functions and the HAVING clause.


SQL Server 2005:

GROUP BY now supported column aliases.

It introduced the COMPUTE and COMPUTE BY clauses, which allowed you to calculate aggregates on subsets of the data.

It supported the GROUPING SETS operator, which allowed you to specify multiple grouping sets in a single query.


SQL Server 2008:

GROUP BY now supported the CUBE and ROLLUP operators, which allowed you to generate subtotals and grand totals for each level of a hierarchy.


SQL Server 2012:

GROUP BY introduced the GROUPING and GROUPING_ID functions, which allowed you to identify different groups, whether a column was included in a grouping set, and to generate a unique identifier for each column name and group.


SQL Server 2016:

GROUP BY introduced the GROUPING SETS syntax, which allowed you to specify and combine multiple group grouping sets in a single query using a simpler syntax than the previous COMPUTE BY and GROUP BY ROLLUP and CUBE syntax.


SQL Server 2017:

GROUP BY added support for the GROUPING SETS WITH ROLLUP and GROUPING SETS WITH CUBE extensions, which allow you to include the ROLLUP and CUBE operators within a GROUPING SETS clause.


SQL Server 2019:

GROUP BY added support for the GROUPING_WINDOW function, which allowed you to generate running aggregates and other windowed functions based on a single group-by query.

Overall, the GROUP BY clause in SQL Server has evolved to become more powerful and flexible over time, allowing for more complex and sophisticated analysis of data.


SQL Server GROUP BY Clause With Aggregate Functions An Where Clause

The GROUP BY clause in SQL Server is used to group rows that have the same values in one or more columns into summary rows, like computing totals or averages. Aggregate functions are commonly used with the GROUP BY clause to perform calculations on the grouped rows of data. Here are some examples of using the GROUP BY clause and aggregate functions in SQL Server:


Here is the T-SQL script to create the "Orders" table in SQL Server, as well as the INSERT statements to populate it with the data from the examples above:

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID varchar(15),
    OrderDate date,
    TotalAmount decimal(10,2),
    OrderCategory varchar(50),
    OrderSalesPerson varchar(50),
    CustomerRegion varchar(50)
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount, OrderCategory, OrderSalesPerson, CustomerRegion)
VALUES 
    (1, 'ABC-123-2022', '2022-01-01', 100.00, 'Wing', 'Milton Friedman', 'North America'),
    (2, 'ABC-123-2022', '2022-01-03', 50.00, 'Engine', 'Amartya Sen', 'Europe'),
    (3, 'DEF-456-2022', '2022-01-04', 75.00, 'Landing Gear', 'John Maynard Keynes', 'North America'),
    (4, 'DEF-456-2022', '2022-01-06', 125.00, 'Propeller', 'Friedrich Hayek', 'Asia'),
    (5, 'GHI-789-2022', '2022-01-08', 200.00, 'Fuselage', 'Milton Friedman', 'North America'),
    (6, 'ABC-123-2023', '2023-02-01', 150.00, 'Wing', 'Amartya Sen', 'North America'),
    (7, 'ABC-123-2023', '2023-02-05', 75.00, 'Engine', 'John Maynard Keynes', 'Europe'),
    (8, 'DEF-456-2023', '2023-02-09', 90.00, 'Landing Gear', 'Friedrich Hayek', 'North America'),
    (9, 'DEF-456-2023', '2023-02-12', 110.00, 'Propeller', 'Milton Friedman', 'Asia'),
    (10, 'GHI-789-2023', '2023-02-15', 250.00, 'Fuselage', 'Amartya Sen', 'Europe'),
    (11, 'JKL-012-2023', '2023-03-01', 300.00, 'Wing', 'John Maynard Keynes', 'North America'),
    (12, 'JKL-012-2023', '2023-03-02', 75.00, 'Engine', 'Friedrich Hayek', 'Europe'),
    (13, 'MNO-345-2023', '2023-03-03', 50.00, 'Landing Gear', 'Milton Friedman', 'North America'),
    (14, 'MNO-345-2023', '2023-03-04', 120.00, 'Propeller', 'Amartya Sen', 'Asia'),
    (15, 'PQR-678-2023', '2023-03-05', 175.00, 'Fuselage', 'John Maynard Keynes', 'North America')

This script will create the "Orders" table with the specified columns and data types, and insert the sample data into the table. You can use similar data and column expressions from this table to run the examples I provided earlier using the GROUP BY clause and aggregate functions.


Example 1:

Get the total amount minimum value of orders per customer.

SELECT CustomerID, SUM(TotalAmount) as TotalOrderAmount
FROM Orders
GROUP BY CustomerID

Example 2:

Get the number of orders per customer.

SELECT CustomerID, COUNT(OrderID) as NumOrders
FROM Orders
GROUP BY CustomerID

Example 3:

Get the average order amount per customer.

SELECT CustomerID, AVG(TotalAmount) as AvgOrderAmount
FROM Orders
GROUP BY CustomerID

Example 4:

Group By Where SQL

Here's an example of a GROUP BY query using the WHERE clause with the sample data:

SELECT CustomerID, SUM(TotalAmount) as TotalSales
FROM Orders
WHERE YEAR(OrderDate) = 2022
GROUP BY CustomerID;

This query selects the CustomerID and the total sales (TotalAmount) for each customer in the Orders table where the OrderDate year is 2022. The GROUP BY clause groups the results by CustomerID, and the SUM() function calculates the total sales for each customer. The WHERE clause filters the results to only include orders placed in 2022.


Compute By

Here's an example of using the COMPUTE BY clause in T-SQL with the Orders table:

SELECT OrderCategory, OrderSalesPerson, SUM(TotalAmount) AS TotalSales
FROM Orders
GROUP BY OrderCategory, OrderSalesPerson WITH COMPUTE BY
ORDER BY OrderCategory, OrderSalesPerson

In this example, we are using the COMPUTE BY clause to add subtotals and a grand total to the result set. The result set will include subtotals for each combination of OrderCategory and OrderSalesPerson, as well as a grand total for all categories and salespersons combined.


The result will have the following columns:

  • OrderCategory

  • OrderSalesPerson

  • TotalSales

The result will be grouped by OrderCategory and OrderSalesPerson, and the COMPUTE BY clause will add subtotals for each combination of OrderCategory and OrderSalesPerson, as well as a grand total for all categories and salespersons combined.

Note that the COMPUTE BY clause can be used with multiple columns in the GROUP BY clause, and can also be combined with the ROLLUP or CUBE clauses to generate more complex subtotals and grand totals.


GROUPING SETS

You would use GROUPING SETS in T-SQL when you want to group data by multiple combinations of columns. With GROUPING SETS, you can specify multiple groupings in a single query, which allows you to get summary data for multiple levels of granularity in a single result set.


For example, suppose you have a sales table with columns for SalesDate, Region, Product, and SalesAmount, and you want to get the total sales for each region and product, as well as the total sales for each region and for all products. You could use GROUPING SETS to achieve this with a single query, like this:

SELECT CustomerRegion, OrderCategory, SUM(TotalAmount) as TotalSales
FROM Orders
GROUP BY GROUPING SETS ((CustomerRegion, OrderCategory), (CustomerRegion), (OrderCategory), ())
ORDER BY CustomerRegion, OrderCategory;

The first GROUP BY grouping set (Region, Product) groups the data by both Region and Product, giving you the total sales for each combination of region and product. The second grouping set (Region) groups the data only by Region, giving you the total sales for each region across all products.


Using GROUPING SETS allows you to consolidate multiple queries into a single query and get the results in a single result set, which can simplify your code and improve performance.


GROUPING SETS with ROLLUP

You would use GROUPING SETS with ROLLUP when you want to compute subtotals and grand totals for multiple groupings in a single query. The ROLLUP operator computes the subtotal and grand total rows for each grouping set defined in the GROUPING SETS clause.

Here's an example using the same Orders table:

SELECT CustomerRegion, OrderCategory, OrderSalesPerson, 
    SUM(TotalAmount) AS TotalSales
FROM Orders
GROUP BY GROUPING SETS (
    (CustomerRegion, OrderCategory, OrderSalesPerson),
    (CustomerRegion, OrderCategory),
    (CustomerRegion),
    ()
) WITH ROLLUP;

In this example, we're using GROUPING SETS with ROLLUP to generate subtotals and grand totals for each level of aggregation. The GROUPING SETS function is used to specify multiple grouping sets, and the WITH ROLLUP modifier generates additional rows that represent subtotals and grand totals.


Group Data By A Query Result A Single Column

Suppose we have a table called "Employees" with the following columns: "EmployeeID", "FirstName", "LastName", "Department", and "Salary". We want to group the employees by their department and calculate the average salary for each department.

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department

Explanation: In this example, we group the employees by their "Department" column and calculate the average salary for each department using the AVG() function.


Group Data By Multiple Columns

Suppose we have a table called "Orders" with the following columns: "OrderID", "CustomerID", "OrderDate", and "TotalAmount". We want to group the orders by both the customer and the year, and calculate the total amount of orders for each combination of customer and year.

SELECT CustomerID, YEAR(OrderDate) AS OrderYear, SUM(TotalAmount) AS TotalAmount
FROM Orders
GROUP BY CustomerID, YEAR(OrderDate)

Explanation: In this example, we group the orders by both the "CustomerID" and the "YEAR(OrderDate)" columns, and calculate the total amount of orders for each combination of customer and year using the SUM() function.


Group Data By An Expression

Suppose we have a table called "Products" with the following columns: "ProductID", "ProductName", "Category", "Price", and "UnitsInStock". We want to group the products by price range and calculate the total number of units in stock for each price range.

SELECT CASE 
         WHEN Price < 50 THEN 'Less than 50'
         WHEN Price BETWEEN 50 AND 100 THEN 'Between 50 and 100'
         ELSE 'More than 100'
       END AS PriceRange,
       SUM(UnitsInStock) AS TotalUnitsInStock
FROM Products
GROUP BY CASE 
           WHEN Price < 50 THEN 'Less than 50'
           WHEN Price BETWEEN 50 AND 100 THEN 'Between 50 and 100'
           ELSE 'More than 100'
         END

Explanation: In this following example below, we use a CASE statement to create an expression that categorizes the products into three price ranges, and then group the products by this same expression together. We calculate the total number of units in stock for each price range using the SUM() function.


Limitations When Using GROUP BY

There are some limitations when using GROUP BY in T-SQL. Here are a few:

Aggregate functions only: The SELECT statement clause can only include aggregate functions or single row or column expressions or columns that are included in the GROUP BY clause. This means you can select statement group by clause but can't select individual rows or columns that are not part of the GROUP BY clause unless they are included in an aggregate function like SUM or COUNT.


Non-deterministic functions: Non-deterministic functions like RAND() cannot be used in the GROUP BY clause.


NULL values: If there are NULL values in the data, the GROUP BY clause will treat multiple group of them as distinct and unique values. This can lead to unexpected results when counting or summing data.


Performance issues: Using GROUP BY on large data sets can be slow and may cause performance issues. It's important to optimize your queries and use indexes to improve performance.


Grouping sets limitations: GROUP BY with grouping sets can result in a large number possible combinations of rows being returned, which can impact performance and increase resource usage.


Order of execution: The order of execution of the clauses in a query is important. the GROUP BY clause should come after the FROM and WHERE clauses, and before the HAVING and ORDER BY clauses. If the clauses are not in the correct order, the following query then may not produce the expected results.


It's important to be aware of these limitations when using GROUP BY in T-SQL and to optimize your queries above query, accordingly.


More Information

Please See Article #1 Grouping With Having clause




Comments

Rated 0 out of 5 stars.
No ratings yet
Add a rating

Get in Touch

Thanks for submitting!

bottom of page