SQL Server: GROUP BY Clause
This SQL Server tutorial 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 = 'Anderson' 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 ‘Anderson’.
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.