Beginners Guide: Group By In SQL Server

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

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

Introduction

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


Conclusion

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

Neerajprasadsharma
Full Name: NEERAJ Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

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: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)