How To Use Having Clause In Mssql

Hello Friend Today In This Blog I Will Show You How To Use Having Clause In Mssql .The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.The HAVING Clause enables you to specify conditions that filter which group results appear in the results.The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.Summary: in this tutorial, you will learn how to use the SQL HAVING clause to specify a search condition for a group of rows or an aggregate.The HAVING clause works like the WHERE clause if it is not used with the GROUP BY clause. The difference between the HAVING clause and the WHERE clause is that the WHERE clause is used to filter rows, while the HAVING clause is used to filter groups of rows.This SQL tutorial explains how to use the SQL HAVING clause with syntax and examples.The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.SQL HAVING clause specifies a search condition for a group or an aggregate. HAVING is usually used in a GROUP BY clause, but even if you are not using GROUP BY clause, you can use HAVING to function like a WHERE clause. You must use HAVING with SQL SELECT.A HAVING clause restricts the results of a GROUP BY in a SelectExpression. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause. It can, however, refer to constants, aggregates, and special registers.Aggregates in the HAVING clause do not need to appear in the SELECT list. If the HAVING clause contains a subquery, the subquery can refer to the outer query block if and only if it refers to a grouping column.having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group based SQL functions, just like WHERE clause.

HAVING Syntax

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

HAVING condition

ORDER BY column_name(s);

Step 1 :Create database

Create database BMC

Step 2:Create table

Here In This step We Will Create table.

Here In This Step We Required Four Column With Name Cids ,Cname ,Ccity And CPrice using Parameter bigint,varchar and float.

create table Client_table_news

(

Cids bigint,

Cname varchar(50),

Ccity varchar(200),

CPrice float

);

Step 3 :Create Insert Trigger Functionality

Here In This Step We Will Insert Data Using Insert Command.

Data Will Be Added Manually Using Insert Command.

Insert Into Client_table_news values(1,'VIRAJ','MUMBAI',15000.00)

Insert Into Client_table_news values(2,'RAJ','DELHI',10000.00)

Insert Into Client_table_news values(3,'ANIL','PUNE',12000.00)

Insert Into Client_table_news values(4,'SUNIL','MUMBAI',9000.00)

Insert Into Client_table_news values(5,'SHERYAS','JAIPUR',11000.00)

Insert Into Client_table_news values(6,'AJAY','MUMBAI',16000.00)

select *from Client_table_news

Step 4 : Use Having Clause 

Here In This Step We Will Use Having Clause Using Parameter Cids By  HAVING COUNT(Cids) > 1;

SELECT COUNT(Cids), Ccity 
FROM Client_table_news
GROUP BY Ccity 

HAVING COUNT(Cids) > 1;

SELECT COUNT(Cids), Ccity
FROM Client_table_news
GROUP BY Ccity
HAVING COUNT(Cids) > 1

ORDER BY COUNT(Cids) DESC;

HERE IS THE VIDEO RELATED TO TOPIC