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.