SQL having statements

HAVING clause

The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.

SQL having syntax

1 SELECT column_name, Aggregate_function (column_name) 2  from table_name 3 WHERE column_name operator Value 4 GROUP  by column_name 5  having aggregate_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 with a total order amount of less than 2000.

We use the following SQL statement:

1 SELECT Customer,SUMfrom  Orders2GROUP by Customer3  havingSUM(orderprice)<

The result set is similar to:

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 have added a common where clause to the SQL statement:

1 SELECTCustomer,SUM(Orderprice) fromOrders2 WHERECustomer='Bush' ORCustomer='Adams'3 GROUP  byCustomer4  having SUM(Orderprice)> the

Result set:

Customer	SUM (Orderprice)
Bush	2000
Adams	2000

SQL having statements