GROUP BY

GROUP BY aggregates the results on the required column names or expressions. Building on the same example from the ORDER BY section, we want to know the overall product quantity per product name and location, from the following code snippet. The Quantity column is using the aggregate function SUM. Therefore, the remaining columns need to be contained in the aggregation GROUP BY clause:

SELECT P.Name AS ProductName, SUM([PI].Quantity) AS Total_Quantity, L.Name AS LocationName
FROM Production.Product AS P
INNER JOIN Production.ProductInventory AS [PI] ON P.ProductID = [PI].ProductID
INNER JOIN Production.Location AS L ON [PI].LocationID = L.LocationID
WHERE P.Name LIKE 'Touring%'
GROUP BY P.Name, L.Name
ORDER BY P.Name DESC, L.Name DESC;

The following screenshot shows the results with one row per set as defined by the GROUP BY clause:

Aggregations can be further specified by using the following keywords:

• ROLLUP: Specifies the creation of subtotals and totals for the required column names or expressions.
• CUBE: Specifies the creation of subtotals and totals for all combinations of columns in the GROUP BY clause.
• GROUPING SETS: Allows the use of multiple GROUP BY clauses, such as using ROLLUP and CUBE together.