SQL SERVER Topic

SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Syntax:- 

  

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value


Example:- We are using SQL SERVER. So, first we create two tables name are tblEmployees and tblSalarys and insert some as shown in below. 



  



CREATE TABLE tblEmployees
(
EmpID INT PRIMARY KEY,EmpName VARCHAR(100),Address varchar(200),MobileNo bigint,Email NVARCHAR(200)
)


INSERT INTO tblEmployees VALUES(1001,'Santosh kumar singh','A-13',87878,'sa@gmail.com')
INSERT INTO tblEmployees VALUES(1002,'Manorma arya','T-13,Bihar',83878,'ma@gmail.com')
INSERT INTO tblEmployees VALUES(1003,'Pooja','G-13',84878,'p@gmail.com')
INSERT INTO tblEmployees VALUES(1004,'Arun kumar','U-13,New Delhi',77878,'a@gmail.com')
INSERT INTO tblEmployees VALUES(1005,'Sourab kumar','D-89,Bihar',9878,'su@gmail.com')
INSERT INTO tblEmployees VALUES(1006,'Suneeta','U-13,New Delhi',74878,'sut@gmail.com')



CREATE TABLE tblSalarys
(
SalaryId int PRIMARY KEY,Salary int,SalaryDate varchar(50) ,EmpId int FOREIGN KEY REFERENCES tblEmployees(EmpId)
)


INSERT INTO tblSalarys VALUES(1,10000,'1 Feb,2015',1001)
INSERT INTO tblSalarys VALUES(2,9000,'1 Feb,2015',1003)
INSERT INTO tblSalarys VALUES(3,8000,'3 March,2015',1002)
INSERT INTO tblSalarys VALUES(4,10000,'1 March,2015',1001)
INSERT INTO tblSalarys VALUES(5,12000,'1 Feb,2015',1004)
INSERT INTO tblSalarys VALUES(6,10000,'5 April,2015',1001)
INSERT INTO tblSalarys VALUES(7,8000,'1 Feb,2015',1006)


tblEmployee table:-


tblSalary table:-




Now, we are using HAVING and get output as shown in below. 

  

SELECT e.EmpName AS Name,e.Email, COUNT(e.EmpID) AS TotalNumber FROM tblEmployees e 
INNER JOIN
tblSalarys s
ON e.EmpID=s.EmpId GROUP BY e.EmpName,e.Email HAVING  COUNT(e.EmpID)>1