ArticleDirectory
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
- Before a group by statement, SQL calculates the where statement before grouping.
- Having statement after the group by statement, SQL calculates the having statement after the group.