SQL Server: GROUP BY Clause

In this post explains how to use the GROUP BY clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

Syntax

The syntax for the GROUP BY clause in SQL Server (Transact-SQL) is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

aggregate_function

It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

Example - Using SUM function

Let's look at a SQL Server GROUP BY query example that uses the SUM function.

For example:

SELECT product_name, SUM(quantity) AS "Total quantity"
FROM products
GROUP BY product_name;

This SQL Server GROUP BY example uses the SUM function to return the product_name and the total quantity (for the product_name).

Because you have listed one column (the product_name field) in your SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY clause. The product_name field must, therefore, be listed in the GROUP BY clause.

Example - Using COUNT function

Let's look at how we could use the GROUP BY clause with the COUNT function.

For example:

SELECT manager_id, COUNT(*) AS "Number of employees"
FROM employees
WHERE last_name = 'Mark'
GROUP BY manager_id;

This GROUP BY example uses the COUNT function to return the manager_id and the number of employees whose last_name is 'Mark'.

Example - Using MIN function

Let's next look at how we could use the GROUP BY clause with the MIN function.

For example:

SELECT product_type, MIN(quantity) AS "Lowest quantity"
FROM products
GROUP BY product_type;

This GROUP BY example uses the MIN function to return the product_type and the minimum quantity for that product_type.

Example - Using MAX function

Finally, let's look at how we could use the GROUP BY clause with the MAX function.

For example:

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

This GROUP BY example uses the MAX function to return the name of each department and the maximum salary in the department.