Beginners Guide: Group By In SQL Server

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 2317 red flag

Learn the basics about group by and aggregate functions in SQL Server


Group By uses in SQL Server with select query to view  column(s) as a group, only one row return for each group specified  and if we want, we can perform aggregate functions for each group.
For example:
We have a product sale table.

Create table ProductSale (ProductId char(1) , customerID int , SaleQty int)
Insert Into ProductSale 
Select 'A' , 101 , 10 union All
Select 'A' , 44 , 2 union All
Select 'A' , 3 , 1 union All
Select 'B' , 44 , 3 union All
Select 'c' , 3 , 5 union All
Select 'c' , 4 , 1

We can perform an aggregate function on a non aggregate column(s), depend on the requirement of the user. For example, we want to know how many products are in the sale, or count how many times a product sold, or sale quantity per product  etc.
Let us see in the below example:

SQL Server Group By Example:

Let us check how many products involve in sale using group by in select query:

Select ProductId From ProductSale
Group By productId

SQL Server Group By with Aggregate Functions

As we have already seen in the above example, the best use of group by is to get data with aggregate functions because we have data in details, but now we don`t want to view all the details, we want some useful aggregate information. 

SQL Server Group By With Count Function

Let us count, how many times products are involved in the sales using the count function.
Select ProductId , [Count Product] =Count_Big(productID)  
From ProductSale Group By productId

In our examples, we are using only one column in group by to make example easy, however we can use more than one column in the group by, and can use more than one aggregate function with group by according to our need.

SQL Server Group By with Sum Function
Consider a scenario where a user wants to see the total sale per product, in this scenario we can use sum as aggregate function with group by product. See the example below:
Select ProductID , [Sale Quantity]=Sum(SaleQty) 
 From ProductSale Group By ProductID

SQL Server Group By With Max Function

Consider another scenario where a user wants to see a maximum quantity of sale per product. In this scenario we can use the Max function to serve the purpose. Example below:

Select ProductID , [Max Quantity]=MAX(SaleQty) 
 From ProductSale Group By ProductID


This article was the introduction of how to use group by and aggregate functions in SQL Server. Hope this article provides the basic idea to use the Group By and aggregate function, if still you have any doubt about this subject you can ask it in the comments section below.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here:

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)