Introduction to SQL Server Group By

Introduction to SQL Server GROUP BY clause

The GROUP BY clause allows you to arrange the rows of a query in groups. The groups are determined by the columns that you specify in the GROUP BY clause.
The following illustrates the GROUP BY clause syntax:
In this query, the GROUP BY clause produced a group for each combination of the values in the columns listed in the GROUP BY clause.
Consider the following example:
SQL Server GROUP BY clause
In this example, we retrieved the customer id and the ordered year of the customers with the customer id one and two.
As you can see clearly from the output, the customer with the id one placed one order in 2016 and two orders in 2018. The customer with id two placed two orders in 2017 and one order in 2018.
Let’s add a GROUP BY clause to the query to see the effect:
SQL Server GROUP BY clause example
The GROUP BY 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.
Functionally speaking, the GROUP BY clause in the above query produced the same result as the following query that uses the DISTINCT clause:

SQL Server GROUP BY - DISTINCT example

SQL Server GROUP BY clause and aggregate functions

In practice, the GROUP BY clause is often used with aggregate functions for generating summary reports.
An aggregate function performs a calculation on a group and returns a unique value per group. For example, COUNT() returns the number of rows in each group. Other commonly used aggregate functions are SUM()AVG() (average), MIN() (minimum), MAX() (maximum).
The GROUP BY clause arranges rows into groups and an aggregate function returns the summary (count, min, max, average, sum, etc.,) for each group.
For example, the following query returns the number of orders placed by the customer by year:
SQL Server GROUP BY clause - expression example
If you want to refer to any column or expression that is not listed in the GROUP BY clause, you must use that column as the input of an aggregate function. 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:

More GROUP BY clause examples

Let’s take some more examples to understand how the GROUP BY clause works.

Using GROUP BY clause with the COUNT() function example

The following query returns the number of customers in every city:
SQL Server GROUP BY - COUNT example
In this example, the GROUP BY clause groups the customers together by city and the COUNT() function returns the number of customers in each city.
Similarly, the following query returns the number of customers by state and city.
SQL Server GROUP BY clause - multiple columns example

Using GROUP BY clause with the MIN and MAX functions example

The following statement returns the minimum and maximum list prices of all products with the model 2018 by brand:
SQL Server GROUP BY - MIN and MAX example
In this example, the WHERE clause is processed before the GROUP BY clause, as always.

Using GROUP BY clause with the AVG() function example

The following statement uses the AVG() function to return the average list price by brand for all products with the model year 2018:
SQL Server GROUP BY - AVG example

Using GROUP BY clause with SUM function example

See the following order_items table:
order_items
The following query uses the SUM() function to get the net value of every order:
SQL Server GROUP BY - SUM example
In this tutorial, you have learned how to use the SQL Server GROUP BY clause to arrange rows in groups by a specified list of columns.

Post a Comment

0 Comments