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.

You can also search these topics using sql command having, sql group by example, sql count rows in table, sql query count rows, sql where count greater than 1, sql having clause.

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 Practical SQL 85 2014 Optimization Siva Kumar
2 Microsoft SQL Server 2012 Bible 205 2014 Optimization Azaghu Varshith
3 Red Gate Guide to SQL Server 195 2011 Performance Ranjani Mai
4 Teach Yourself SQL 95 2012 Performance Devi Mai
5 Natural language Query To SQL 185 2009 Performance Hanumanthan
6 Pro Oracle Administration 110 2007 Optimization Siva Kumar

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
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
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
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
Optimization 2014 290
You can also search these topics using difference between where and having clause, having sql example, sql having statement, sql having example, sql query having count, sql having count example.