SQL Having Clause

 
When we want to use Checking or Condition upon an aggregate column with Group By clause in Select statement then we cannot use Where statement with aggregate column, in this case we use Having clause.
 

Summery

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

 

Example

Suppose we have the following Employee records:

 

Employee IdEmpNameAgeDepartmentSalary
1Ankur28Sales2500.00
2David32Sales3000.00
3John30HR2200.00
4James45Production4200.00
5Mohan35Production4300.00
6Ram40HR2000.00
7McDonald30HR2600.00
8Rohan23Sales1600.00

 

If we want to get the Sum of Salary Greater than 7000 of each department then we can use the Having clause with Group By Clause as follows

SELECT Department,Sum([Salary]) as Salary
FROM [EmployeeDatabase].[dbo].[tbl_Employee]
group by department
having Sum([Salary]) > 7000

 
And you will get the following Result
 

DepartmentSalary
Production8500.00
Sales7100.00

 

Author: Ankur

Have worked primarily in the domain of Calling, CRM and direct advertisers services. My technological forte is Microsoft Technologies especially Dot Net (Visual Studio 2003, 2005, 2008, 2010 and 2012) and Microsoft SQL Server 2000,2005 and 2008 R2. My Area of Expertise is in C#. Net, VB.Net, MS-SQL Server, ASP. Net, Silverlight, HTML, XML, Crystal Report, Active Reports, Infragistics, Component Art, ComponeOne, Lead Tools etc.