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 

 
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

 

MIN() – function returns the minimum of the numeric column of specified criteria

Example

  1. Select Min(Price) As 'MinimumCostOfModel' from VehicleProduction   

Output

 

SUM() - function returns the total sum of a numeric column of specified criteria.

Example

  1. Select SUM(Price) As 'SumCostOfAllModel' from VehicleProduction   

Output

 

AVG() - function returns the average value of a numeric column of specified criteria.

Example

  1. Select AVG(Price) As 'AverageCostOfModel' from VehicleProduction   

Output

 

COUNT() - function returns the number of rows that match specified criteria.

Example

  1. Select Count(Price) As 'TotalVehicleModels' from VehicleProduction   

Output

 

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

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 

 

Let’s look at an example of 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

 

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

 

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

 

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

 

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

 

Conclusion

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.