Group By, Having, and Where Clauses In SQL

Jitendra Waghale

15 Comments

Join the conversation! Your thoughts help the community grow.

Raj Kumar8y ago
Most Useful Query To Fetch Data.
Jignesh Kumar8y ago
Nice atrticle short and sweet.
Muhammad Aqib Shehzad8y ago
Simple and nice
Raj Kumar8y ago
This is simplest way to define all query.This query is very helpful.
Manikandan Vengadasalam8y ago
Nice Article Thanks for sharing
- Jitendra Waghale8y ago
  Thank You!!
Shrimant Telgave8y ago
Nice sharing..............keep it up.
- Jitendra Waghale8y ago
  Thank you Shrimant!!
Sushil Singh8y ago
Is ever 'having' clause works as where clause? is yes then why 'where' clause is created if having clause also works as where clause.
- Jitendra Waghale8y ago
  As i explained in example, in some scenario we use where clause before group by clause but you must use having clause after group by or whenever you are dealing with aggregate function condition otherwise you will get error “Incorrect syntax near the keyword 'where'.”
- Jitendra Waghale8y ago
  Select * from dual having 1=1, Execute this query, You will get the answer
- Usama Shahid8y ago
  Hy Sushil, let me tell you the answer of your question. Yes we can use Having instead of WHERE clause but actually HAVING clause is made up for the aggregate function conditions. When we're querying which contains aggregate function then we'll use Group by and if we want to apply some condition like AVG(Amount) > 500, we can't apply this condition in WHERE clause. We have to put this condition in HAVING clause otherwise you'll see the error. One more thing, performance wise WHERE clause is better. So if the conditions that can be filter with WHERE clause, kindly go ahead with WHERE clause and if you really need HAVING clause then you can use HAVING clause. I think I've answered to your question. :)

Introduction

In this blog, we will discuss how to work with SQL 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.

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

Create table VehicleProduction    
(    
Id int primary key Identity,     
Model varchar(50),    
Price money    
)    
    
Insert into VehicleProduction values('L551', 850000),('L551', 850000),('L551', 850000),('L551', 750000),    
('L538', 650000),('L538', 650000),('L538', 550000),('L530', 450000),('L530',350000), ('L545', 250000)    
    
Select * from VehicleProduction

Output

SQL Aggregate Functions

MAX()- function returns the maximum value of the numeric column of specified criteria.

Example

Select max(Price) As 'MaximumCostOfModel' from VehicleProduction

Output

SQL Max()

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

Example

Select Min(Price) As 'MinimumCostOfModel' from VehicleProduction

Output

SQL Min()

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

Example

Select SUM(Price) As 'SumCostOfAllModel' from VehicleProduction

Output

SQL Sum()

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

Example

Select AVG(Price) As 'AverageCostOfModel' from VehicleProduction

Output

SQL Avg()

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

Example

Select Count(Price) As 'TotalVehicleModels' from VehicleProduction

Output

SQL Count()

SQL Distinct clause

The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria.

Example

Select Distinct(Model),  Price from VehicleProduction

Output

SQL Distinct clause

SQL 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

SELECT Column1, Column2    
FROM TableName    
GROUP BY Column1, Column2

Example

Select * from VehicleProduction     
    
Select Model, Price from VehicleProduction     
group by Model, Price

Output

SQL Group by clause

Let’s look at an example of a GROUP BY with aggregate functions.

SQL GROUP BY with aggregate functions

Example

Select Model, Price, Count(*) As QtyOfModel, Sum(Price) As TotPriceOfModel from VehicleProduction
group by Model, Price

Output

SQL GROUP BY with aggregate functions

SQL Where clause

Where clause works with select clause but won’t work on the group by or aggregate function condition.

Example 1

Select Model, Price from VehicleProduction     
where Model != 'L530'    
group by Model, Price

Output

SQL Where clause

Example 2

We can’t use where clause after group by clause

Select Model, Price from VehicleProduction     
group by Model, Price     
where Model != 'L530'

Output

SQL Where clause

SQL Having clause

Having clause works with a group by clause but specifically works on aggregate function condition.

Example

Select Model, Price from VehicleProduction     
Group by Model, Price     
Having SUM(Price)  > 600000.00

Output

SQL Having clause

SQL ORDER BY clause

Order By clause shows the records in ascending or descending order of the specific condition.

Example

Select Model, Price from VehicleProduction     
Group by Model, Price     
Having SUM(Price)  > 400000.00     
order by Price desc

Output

SQL ORDER BY clause

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.