How to apply Having Clause with Group by in Select Query - SQL Server / TSQL Tutorial Part 131

Scenario:

You are working as SQL Server developer, you are asked to write a query that should return Total SaleAmount from dbo.Customer table by CountryShortName. You are also asked to filter the records where Total SaleAmount by CountryShortName is greater than 10.

Solution:

From above scenario, you have noticed couple of things. First we need to sum the SaleAmount. Second we need to group by the SaleAmount by CountryShortName. The Last thing we need to filter those records after calculating the sum and only returns where total SaleAmount is greater than 10. We can not use Where clause here as where will filter the records before Group by. SQL Server provide us Having clause that we can use to filter the records after group by.

Let's create dbo.Customer Table with below records and then write our query by using Group by and Having clause.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)

Let's write our query by using Group by and then use Having to filter the record where Sum(SaleAmount) is greater than 10.

SELECT countryshortname, 
       Sum(saleamount) SaleAmountByCountry 
FROM   dbo.customer 
GROUP  BY countryshortname 
HAVING Sum(saleamount) > 10 

How to filter aggregated data by using Having clause  in SQL 

Video Demo : How to use Having Clause in SQL Select Statement