SQL having clause

ArticleDirectory

• SQL having syntax

References

Http://www.w3school.com.cn/ SQL/SQL _having.asp

Having clause

The reason for adding the having clause in SQL is,The where keyword cannot be used with the aggregate function..

SQL having syntax

 
SelectColumn_name, aggregate_function (column_name)FromTable_nameWhereColumn_name operator ValueGroup ByColumn_nameHavingAggregate_function (column_name) operator Value
 

SQL having instance

We have the following "orders" table:

O_id	Orderdate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find customers whose total order amount is less than 2000.

We use the following SQL statement:

SelectCustomer,Sum(Orderprice)FromOrdersGroup ByCustomerHaving Sum(Orderprice)<2000
 

The result set is similar:

Customer	Sum (orderprice)
Carter	1700

Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.

We add a common where clause to the SQL statement:

SelectCustomer,Sum(Orderprice)FromOrdersWhereCustomer='Bush' OrCustomer='Adams'Group ByCustomerHaving Sum(Orderprice)>1500
 

Result set:

Customer	Sum (orderprice)
Bush	2000
Adams	2000

Usage of where and having statements

1. Before a group by statement, SQL calculates the where statement before grouping.  
2. Having statement after the group by statement, SQL calculates the having statement after the group.