GROUP BY (Transact‑SQL)

The GROUP BY clause groups rows that have the same values in specified columns into aggregate data such as COUNT, SUM, AVG, MAX, or MIN. It is often used in conjunction with aggregate functions to produce summary results.

Syntax

SELECT 
    select_list 
FROM 
    table_expression 
[ WHERE 
    search_condition ] 
[ GROUP BY 
    { grouping_column_expression 
    | rollup_expression 
    | cube_expression } 
    [ ,...n ] 
    [ WITH ROLLUP ] 
    [ HAVING 
        search_condition ] ] 
[ ORDER BY 
    order_by_expression [ ASC | DESC ] [ ,...n ] ]

Remarks

All columns in the SELECT list that are not part of an aggregate function must appear in the GROUP BY clause.
When WITH ROLLUP is specified, an additional row is added that represents the grand total.
Combining GROUP BY with HAVING filters groups after aggregation.
The order of columns in GROUP BY does not affect the results, but it can affect performance in some cases.

Examples

Basic Grouping

SELECT 
    DepartmentID, 
    COUNT(*) AS EmployeeCount, 
    AVG(Salary) AS AvgSalary 
FROM 
    Employees 
GROUP BY 
    DepartmentID
ORDER BY 
    EmployeeCount DESC;

Note: The ORDER BY clause sorts the final grouped result set, not the rows before grouping.

Using WITH ROLLUP

SELECT 
    Country, 
    YEAR(OrderDate) AS OrderYear, 
    SUM(TotalAmount) AS YearlySales 
FROM 
    Orders 
WHERE 
    OrderDate >= '2023-01-01' 
GROUP BY 
    Country, YEAR(OrderDate) WITH ROLLUP
ORDER BY 
    Country, OrderYear;

GROUP BY (Transact‑SQL)

Syntax

Remarks

Examples

Basic Grouping

Using WITH ROLLUP

See also