SQL HAVING Clause


The SQL HAVING clause was added to use with aggregate functions insteadof SQL WHERE clause.

The SQL HAVING clause enables you to define conditions that filter which group results appear in the final results.

In the SQL HAVING clause statement must contains atleast one aggregate function and SQL GROUP BY clause in the SQL query.

The SQL HAVING clause can be applied in any valid SQL GROUP BY statement.


Difference Between HAVING And WHERE Clause

  • SQL WHERE clause can be applied only on selected columns.
  • SQL HAVING clause can be applied only on groups created by the SQL GROUP BY clause.

SQL HAVING Syntax

The basic syntax is:


SELECT column_name1, column_name2, aggregate_function(column_name)
FROM table_name
GROUP BY column_name1, column_name2
HAVING Aggregate_Function_Based_Condition;

The order of having statement will look the below statement:


SELECT column_name1, column_name2, aggregate_function(column_name)
FROM table_name
[WHERE condition]
GROUP BY column_name1, column_name2
HAVING Aggregate_Function_Based_Condition
[ORDER BY column_name1, column_name2];

Note:

  • In every GROUP BY statement must contain atleast one aggregate function.
  • HAVING clause does not include the specified expression as part of an aggregate function.

Sample Database Table - Books

BookId BookName BookPrice RelYear DomainName AuthorName
1 SQL Server 2012 Black Book 105 2006 Administration Bala Murugan
2 Troubleshooting SQL Server 60 2008 Database Vidyavathi
3 Practical SQL 85 2014 Optimization Siva Kumar
4 Microsoft SQL Server 2012 Bible 205 2014 Optimization Azaghu Varshith
5 Red Gate Guide to SQL Server 195 2011 Performance Ranjani Mai
6 SQL Fundamentals 71.87 2015 Administration Sakunthala
7 Teach Yourself SQL 95 2012 Performance Devi Mai
8 Natural language Query To SQL 185 2009 Performance Hanumanthan
9 MySQL: The Complete Reference 199.97 2006 Administration Azaghu Varshith
10 Professional MySql 120 2007 Database Sakunthala
11 Pro Oracle Administration 110 2007 Optimization Siva Kumar
12 How to Write Accurate SQL Code 150 2014 Administration Padmavathi

SQL HAVING Example 1

The following SQL statement will calculates total price into "DomainName" wise from the "Books" table:


SELECT DomainName, SUM(BookPrice) As 'Total Price' 
FROM Books 
GROUP BY DomainName;

Note: The above sql statement did not include having clause.

The result of above query is:

DomainName Total Price
Administration 526.84
Database 180
Optimization 400
Performance 475

The following SQL statement will calculates total price into "DomainName" wise and "Total Price > 400" from the "Books" table:


SELECT DomainName, SUM(BookPrice) As 'Total Price' 
FROM Books 
GROUP BY DomainName
HAVING SUM(BookPrice) > 400;

The result of above query is:

DomainName Total Price
Administration 526.84
Performance 475

In the above statement, we have used HAVING clause to filter the group result.


SQL HAVING Example 2

The following SQL statement will calculates total price into "DomainName" and "RelYear" wise from the "Books" table:


SELECT DomainName, RelYear, SUM(BookPrice) As 'Total Price' 
FROM Books 
GROUP BY DomainName, RelYear;

The result of above query is:

DomainName RelYear Total Price
Administration 2006 304.97
Administration 2014 150
Administration 2015 71.87
Database 2007 120
Database 2008 60
Optimization 2007 110
Optimization 2014 290
Performance 2009 185
Performance 2011 195
Performance 2012 95

The following SQL statement will calculates total price into "DomainName" and "RelYear" wise and "Count(RelYear)" = 2 from the "Books" table:


SELECT DomainName, RelYear, SUM(BookPrice) As 'Total Price' 
FROM Books 
GROUP BY DomainName, RelYear
HAVING COUNT(RelYear) = 2;

The result of above query is:

DomainName RelYear Total Price
Administration 2006 304.97
Optimization 2014 290