Having clause in sql

Having clause in sql.

Having clause: It is used to apply some more condition on SQL statement. Sometimes user need to display very certain result. At this situation Having clause can be used. Having clause is used with SQL aggregate functions. The aggregate functions are MAX, MIN, MIN, COUNT etc.

Group by statement have to use with Having clause.

Having clause Syntax:

SELECT column_name, aggregate_funcation

FROM  table_name

GROUP BY column_name

HAVING aggregate_function

HAVING CLAUSE Example

Create Table Sales
(
ItemCode int NOT NULL Constraint pkItem primary key,
OrderId int NOT NULL,
Name varchar(20)NOT NULL,
MobileNo varchar(20)NOT NULL,
Zipcode varchar(30) NOT NULL,
SoldItem int NOT NULL,
Price float NOT NULL
)
Insert into Sales Values('1','1','iPhone','23434423','343','1','200')
Insert into Sales Values('2','2','Samsung mobile','23434423','343','1','300')
Insert into Sales Values('3','3','Apple','23434423','343','1','250')
Insert into Sales Values('4','4','iPhone','23434423','343','1','180')
Insert into Sales Values('5','5','Samsung mobile','23434423','343','1','250')
Insert into Sales Values('6','6','iPhone','23434423','343','1','220')
Insert into Sales Values('7','7','Samsung mobile','23434423','343','1','300')
Insert into Sales Values('8','8','Samsung mobile','23434423','343','1','205')

Select * from Sales

Result:

ItemCode	OrderId	Name	MobileNo	Zipcode	SoldItem	Price
1	1	iPhone	23434423	343	1	200
2	2	Samsung mobile	23434423	343	1	300
3	3	Apple	23434423	343	1	250
4	4	iPhone	23434423	343	1	180
5	5	Samsung mobile	23434423	343	1	250
6	6	iPhone	23434423	343	1	220
7	7	Samsung mobile	23434423	343	1	300
8	8	Samsung mobile	23434423	343	1	205

HAVING CLAUSE statement

Select AVG(Price)AveragePrice from Sales group by Name  having count(Name)>=3

Above HAVING CLAUSE statement will display,
If number of items are >=3 then average price is displayed.
 Result:

AveragePrice

200

263.75

Select Name,AVG(Price) as AveragePrice From Sales group by Name Having AVG(Price)<=250

Above HAVING CLAUSE statement will display, If average price is >=250 then result will display.
Result:

Name	AveragePrice
Apple	250
iPhone	200

HAVING CLAUSE Example in video





HAVING CLAUSE Example in image