How to use GROUP BY Clause in SQL Server

by

In this SQL Server tutorial, you will learn how to use the GROUP BY clause in SQL Server to group the rows of data.

You will understand the scenarios where you can apply the GROUP BY clause in SQL Server. Then the syntax of the GROUP BY clause. After that, with practical examples, you will understand how the GROUP BY clause works with single or multiple columns.

GROUP BY Clause in SQL Server

The GROUP BY clause separates the rows with the same value into a group based on the specified column. This clause is usually used with aggregate functions such as AVG(), MIN(), MAX(), etc, to operate on each group of rows.

The main goal of the GROUP BY clause is to summarize the information of the table based on one or more columns. You can use the GROUP BY clause in different ways.

  • Businesses can use the GROUP BY clause in the query to group the sales according to product, region, or salesperson. For example, it can help compute the total and average sales per product, region, or salesperson.
  • Also, it can help in grouping the time-based data, such as daily, monthly, and yearly sales. If you have a record (data) of customer purchasing, then you can group the customers by age and location and analyse the purchase patterns of those customers.
  • In inventory management, you can use the GROUP BY clause to group the inventor items based on the category, supplier, location, etc. Additionally, in companies, it can be used for grouping the employees based on the department positions.
  • In healthcare, you can group the patients based on the disease using the GROUP BY clause.

The syntax is given below.

SELECT column_1, column_2,.., aggregate_function_name(column_name)
FROM table_name
WHERE condition
GROUP BY column_1, column_2

Where,

  • GROUP BY column_1, column_2: column_1 and column_2 are the columns based on which the rows of the table are grouped.
  • aggregate_function_name(column_name): The specified aggregate function such MIN(), MAX(), is applied on the column_name.

Let’s see with an example; suppose you have a Sales table shown below.

GROUP BY Clause in SQL Server Sales Table

Look at the table columns such as product_id, sale_amount, or sale_date. Now, think that you need to count the number of sales amount to each product_id. You can do that by using the GROUP BY clause. For that, use the below query.

SELECT product_id,COUNT(sale_amount) AS NumberofSale
FROM Sales
GROUP BY product_id;
GROUP BY Clause in SQL Server

You can see the number of sales amount for each product in the output of the above query in the picture. For example, the number of sale amounts for the product with ID equal to P101 is 3, P105 is 2, etc.

The above query first groups all the sale amounts in the Sales table by a product_id column using the GROUP BY product_id and then applies the COUNT() function (an aggregate function) on each group to count the sales amount.

So here, the GROUP BY clause groups the rows based on the product_id column values. If you look closely at the Sales table product_id column, you find identical product IDs. As you know, the GROUP BY clause groups the rows based on identical values.

But here, you have grouped the rows based on a single column. This is how to use the GROUP BY clause in SQL Server.

GROUP BY Clause in SQL Server with Multiple Columns

In the previous section, you have used the GROUP BY column with a single column, but you can also specify more than one column to group the table data. Simply put, you can group the rows of the table based on multiple columns by specifying these columns with the GROUP BY clause.

For example, you have a SalesData table with ProductID, SalesAmount, and Region columns, as shown below.

GROUP BY Clause in SQL Server with SalesData Table Multiple Columns

Using the above table, you need to find the total sales for each product in each region. To find that, use the below query.

SELECT ProductID, Region, SUM(SalesAmount) as TotalSales
FROM SalesData
GROUP BY ProductID, Region;
GROUP BY Clause in SQL Server with Multiple Columns

From the output of the above query, total sales are computed for each combination of product and region. You need to understand the query part GROUP BY ProductID, Region.

In the query, the rows are first grouped by ProductID, then by Region; after that, the total sale is computed using the SUM(SalesAmount) for each group; here, SUM() is the aggregate function. Thus, you have grouped the data of the SalesData table based on the two columns, ProductID and Region.

This is how to use the GROUP BY clause in SQL Server with more than one column.

Conclusion

In this SQL Server tutorial, you learned how to group the rows in the table based on the specified columns using the GROUP BY clause in SQL Server. Also, you learned where you can use the GROUP BY clause. Additionally, you have used the GROUP BY clause with single and multiple columns to group the data.

You may like to read: