{"id":213,"date":"2018-04-23T21:10:33","date_gmt":"2018-04-23T14:10:33","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=213"},"modified":"2024-08-16T19:12:58","modified_gmt":"2024-08-16T12:12:58","slug":"sql-server-group-by","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-group-by\/","title":{"rendered":"SQL Server GROUP BY Clause"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server GROUP BY<\/code> clause to arrange rows in groups by one or more columns.<\/p>\n\n\n\n

Introduction to SQL Server GROUP BY clause #<\/a><\/h2>\n\n\n\n

The GROUP BY<\/code> clause allows you to arrange the rows of a query<\/a> in groups. The groups are determined by the columns that you specify in the GROUP BY<\/code> clause.<\/p>\n\n\n\n

The following illustrates the GROUP BY<\/code> clause syntax:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    column_name1,\n    column_name2 ,...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this query, the GROUP BY<\/code> clause produces a group for each combination of the values in the columns listed in the GROUP BY<\/code> clause.<\/p>\n\n\n\n
Consider the following example:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    YEAR<\/span> (order_date) order_year\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (1<\/span>, 2<\/span>)\nORDER<\/span> BY<\/span>\n    customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, we retrieve the customer id and the ordered year of the customers with customer id 1 and 2.<\/p>\n\n\n\n
The output indicates that the customer with id 1 placed one order in 2016 and two orders in 2018. The customer id 2 placed two orders in 2017 and one order in 2018.<\/p>\n\n\n\n
Let’s add a GROUP BY<\/code> clause to the query to see the effect:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    YEAR<\/span> (order_date) order_year\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (1<\/span>, 2<\/span>)\nGROUP<\/span> BY<\/span>\n    customer_id,\n    YEAR<\/span> (order_date)\nORDER<\/span> BY<\/span>\n    customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The GROUP BY<\/code> clause arranged the first three rows into two groups and the next three rows into the other two groups with the unique combinations of the customer id and order year.<\/p>\n\n\n\n
Functionally speaking, the GROUP BY<\/code> clause in the above query produced the same result as the following query that uses the DISTINCT<\/a><\/code> clause:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n    customer_id,\n    YEAR<\/span> (order_date) order_year\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (1<\/span>, 2<\/span>)\nORDER<\/span> BY<\/span>\n    customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure> #<\/a><\/h2>\n\n\n\n
SQL Server GROUP BY clause and aggregate functions #<\/a><\/h2>\n\n\n\n
In practice, the GROUP BY<\/code> clause is often used with aggregate functions<\/a> for generating summary reports.<\/p>\n\n\n\n
An aggregate function<\/a> performs a calculation on a group and returns a unique value per group. For example, COUNT()<\/a><\/code> returns the number of rows in each group. Other commonly used aggregate functions are SUM()<\/a><\/code>, AVG()<\/code><\/a> (average), MIN()<\/a><\/code> (minimum), MAX()<\/a><\/code> (maximum).<\/p>\n\n\n\n
The GROUP BY<\/code> clause arranges rows into groups and an aggregate function returns the summary (count, min, max, average, sum, etc.,) for each group.<\/p>\n\n\n\n
For example, the following query returns the number of orders placed by the customer by year:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    YEAR<\/span> (order_date) order_year,\n    COUNT<\/span> (order_id) order_placed\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (1<\/span>, 2<\/span>)\nGROUP<\/span> BY<\/span>\n    customer_id,\n    YEAR<\/span> (order_date)\nORDER<\/span> BY<\/span>\n    customer_id; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
If you want to reference a column or expression that is not listed in the GROUP BY<\/code> clause, you must use that column as the input of an aggregate function<\/a>. Otherwise, you will get an error because there is no guarantee that the column or expression will return a single value per group. For example, the following query will fail:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    YEAR<\/span> (order_date) order_year,\n    order_status\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (1<\/span>, 2<\/span>)\nGROUP<\/span> BY<\/span>\n    customer_id,\n    YEAR<\/span> (order_date)\nORDER<\/span> BY<\/span>\n    customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
More GROUP BY clause examples #<\/a><\/h2>\n\n\n\n
Let’s take some more examples to understand how the GROUP BY<\/code> clause works.<\/p>\n\n\n\n
1) Using GROUP BY clause with the\u00a0COUNT() function example #<\/a><\/h3>\n\n\n\n
The following query returns the number of customers in every city:<\/p>\n\n\n
SELECT<\/span>\n    city,\n    COUNT<\/span> (customer_id) customer_count\nFROM<\/span>\n    sales.customers\nGROUP<\/span> BY<\/span>\n    city\nORDER<\/span> BY<\/span>\n    city;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the GROUP BY<\/code> clause groups the customers by city and the COUNT()<\/code> function returns the number of customers in each city.<\/p>\n\n\n\n
Similarly, the following query returns the number of customers by state and city.<\/p>\n\n\n
SELECT<\/span>\n    city,\n    state,\n    COUNT<\/span> (customer_id) customer_count\nFROM<\/span>\n    sales.customers\nGROUP<\/span> BY<\/span>\n    state,\n    city\nORDER<\/span> BY<\/span>\n    city,\n    state;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
2) Using GROUP BY clause with the MIN and MAX functions example #<\/a><\/h3>\n\n\n\n
The following statement returns the minimum and maximum list prices of all products with the model 2018 by brand:<\/p>\n\n\n
SELECT<\/span>\n    brand_name,\n    MIN<\/span> (list_price) min_price,\n    MAX<\/span> (list_price) max_price\nFROM<\/span>\n    production.products p\nINNER<\/span> JOIN<\/span> production.brands b ON<\/span> b.brand_id = p.brand_id\nWHERE<\/span>\n    model_year = 2018<\/span>\nGROUP<\/span> BY<\/span>\n    brand_name\nORDER<\/span> BY<\/span>\n    brand_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the WHERE<\/code> clause is processed before the GROUP BY<\/code> clause, as always.<\/p>\n\n\n\n
3) Using GROUP BY clause with the\u00a0AVG() function example #<\/a><\/h3>\n\n\n\n
The following statement uses the AVG()<\/code> function to return the average list price by brand for all products with the model year 2018:<\/p>\n\n\n
SELECT<\/span>\n    brand_name,\n    AVG<\/span> (list_price) avg_price\nFROM<\/span>\n    production.products p\nINNER<\/span> JOIN<\/span> production.brands b ON<\/span> b.brand_id = p.brand_id\nWHERE<\/span>\n    model_year = 2018<\/span>\nGROUP<\/span> BY<\/span>\n    brand_name\nORDER<\/span> BY<\/span>\n    brand_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
4) Using GROUP BY clause with the SUM function example #<\/a><\/h3>\n\n\n\n
See the following order_items<\/code> table:<\/p>\n\n\n\n
\"order_items\"<\/figure>\n\n\n\n
The following query uses the SUM()<\/code> function to get the net value of every order:<\/p>\n\n\n
SELECT<\/span>\n    order_id,\n    SUM<\/span> (\n        quantity * list_price * (1<\/span> - discount)\n    ) net_value\nFROM<\/span>\n    sales.order_items\nGROUP<\/span> BY<\/span>\n    order_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SQL Server GROUP BY<\/code> clause to arrange rows in groups by a specified list of columns.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t