Group By, Having, and Where Clauses In SQL

Introduction

 
In this blog, we will discuss how to work with GROUP BY, WHERE, and HAVING clauses in SQL and explain the concept with an example in a simple way. I hope this is very useful for beginners and intermediates to help them understand the basic concept.
 

Group by clause

 
The Group by clause is often used to arrange identical duplicate data into groups with a select statement to group the result-set by one or more columns. This clause works with the select specific list of items, and we can use HAVING, and ORDER BY clauses. Group by clause always works with an aggregate function like MAX, MIN, SUM, AVG, COUNT.
 
Let us discuss group by clause with an example. We have a VehicleProduction table and there are some models with a price and it has some duplicate data. We want to categorize this data in a different group with a respective total price.
 
Example
  1. Create table VehicleProduction    
  2. (    
  3. Id int primary key Identity,     
  4. Model varchar(50),    
  5. Price money    
  6. )    
  7.     
  8. Insert into VehicleProduction values('L551', 850000),('L551', 850000),('L551', 850000),('L551', 750000),    
  9. ('L538', 650000),('L538', 650000),('L538', 550000),('L530', 450000),('L530',350000), ('L545', 250000)    
  10.     
  11. Select * from VehicleProduction    
Output
 
vehuprodexam.png
 

Aggregate Functions

 
MAX()- function returns the maximum value of the numeric column of specified criteria.
 
Example
  1. Select max(Price) As 'MaximumCostOfModel' from VehicleProduction    
Output
 
Max.png
 
MIN()- function returns the minimum of the numeric column of specified criteria.
 
Example
  1. Select Min(Price) As 'MinimumCostOfModel' from VehicleProduction    
Output
 
Min.png
 
SUM()- function returns the total sum of a numeric column of specified criteria.
 
Example
  1. Select SUM(Price) As 'SumCostOfAllModel' from VehicleProduction    
Output
 
Sum.png
 
AVG()- function returns the average value of a numeric column of specified criteria.
 
Example
  1. Select AVG(Price) As 'AverageCostOfModel' from VehicleProduction    
Output
 
Avg.png
 
COUNT()- function returns the number of rows that match specified criteria.
 
Example
  1. Select Count(Price) As 'TotalVehicleModels' from VehicleProduction    
Output
 
Count.png
 

Distinct clause

 
The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria.
 
Example
  1. Select Distinct(Model),  Price from VehicleProduction    
Output
 
Distict.png
 

Group by clause

 
The Group by clause is often used to arrange the identical duplicate data into groups with the select statement. This clause works with the select specific list of items, for that we can use HAVING, and ORDER BY clauses.
 
Syntax
  1. SELECT Column1, Column2    
  2. FROM TableName    
  3. GROUP BY Column1, Column2    
Example
  1. Select * from VehicleProduction     
  2.     
  3. Select Model, Price from VehicleProduction     
  4. group by Model, Price    
Output
 
Simple%20Group%20by.png
 
Let’s look at an example of a GROUP BY with aggregate functions.
 

GROUP BY with aggregate functions

Example
  1. Select Model, Price, Count(*) As QtyOfModel, Sum(Price) As TotPriceOfModel  from VehicleProduction     
  2. group by Model, Price  
Output
 
Group%20by%20with%20Aggregate.png
 

Where clause

 
Where clause works with select clause but won’t work on the group by or aggregate function condition.
 
Example 1
  1. Select Model, Price from VehicleProduction     
  2. where Model != 'L530'    
  3. group by Model, Price  
Output
 
/With%20Where.png
 
Example 2
 
We can’t use where clause after group by clause
  1. Select Model, Price from VehicleProduction     
  2. group by Model, Price     
  3. where Model != 'L530'    
Output
 
With%20Error%20Where.png
 

Having clause

 
Having clause works with a group by clause but specifically works on aggregate function condition.
 
Example
  1. Select Model, Price from VehicleProduction     
  2. Group by Model, Price     
  3. Having SUM(Price)  > 600000.00   
Output
 
Having.png
 

ORDER BY clause

 
Order By clause shows the records in ascending or descending order of the specific condition.
 
Example
  1. Select Model, Price from VehicleProduction     
  2. Group by Model, Price     
  3. Having SUM(Price)  > 400000.00     
  4. order by Price desc    
Output
 
Order%20by.png
 

Summary

 
I hope you understand the concept, please post your feedback, questions, or comments about this blog and feel free to tell me the required changes in this write-up to improve the content quality.