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






2 comments:

  1. Candidates who are Going to Appear in NIOS 9th can easily get Qualified by Preparing through NIOS 9th Solved Question Paper 2023 Pdf. Without wasting more time, Comprehend the Underneath NIOS 9th Class Question Paper 2023 segment of this page of as it contains entire details regarding NIOS 9th Sample Paper 2023 Download pdf version, National Open School High School Students Comprises Questions 2023 that can be asked in the Examinations. NIOS 9th Model Test Paper 2023 are available for each and every subject at the beneath section, so please have a look.

    ReplyDelete