Using the GROUP BY Keyword

The GROUP BY keyword divides the query results into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group. The arguments to GROUP BY must be one or more columns or non-aggregate expressions.

The following example calculates the total for all orders per calendar year.

Example:

SELECT SUM(Quantity * UnitPrice * (1 - Discount)) AS Total, year(OrderDate) AS Year FROM OrderDetails AS od INNER JOIN Orders AS o ON od.OrderID = o.OrderID GROUP BY year(OrderDate)

When aggregate functions are used in the SELECT, the GROUP BY keyword can be omitted: in this case the BI Server will automatically group by all the columns specified in the SELECT that do not have an aggregate function specified.

The following query calculates the average unit price for each category.

Example:

SELECT toformat(AVG(UnitPrice),'n') AS AveragePrice, CategoryName FROM Products AS p INNER JOIN Categories AS c ON p.CategoryID = c.CategoryID

This notation requires projecting all the columns for which we want to group by: in order to group by a column that is not projected the GROUP BY keyword is required.

Although this syntax is supported for backwards compatibility, it is recommended to always specify the GROUP BY keyword in order to explicitly define the group by columns.