SQL Having Clause
The SQL Having Clause is used with aggregate function like sum(), avg(), min(), max(), etc. It is used to filter the resultset. Having is a replacement for WHERE clause as WHERE clause can not be used with aggregate functions.
SQL Having clause Example:
Let's continue with our example of having SQL database with orders table.
|100001||Jwalant Patel||200.00||Pending||02/06/2013 09:15:52 AM|
|100002||Hiren Amin||400.00||Processing||02/07/2013 08:25:17 PM|
|100003||Dharmendra Chavda||300.00||Processing||02/08/2013 11:36:42 AM|
|100004||Dharmendra Chavda||300.00||Processing||02/08/2013 10:33:41 PM|
|100005||Hiren Amin||100.00||Processing||02/08/2013 09:36:43 AM|
|100006||Jwalant Patel||100.00||Processing||02/08/2013 07:16:32 AM|
You want to get all customers who placed orders and order total is above $400.
Use following syntax of SQL Having clause to get the list of customers whose total orders are above $400.
SELECT customer_name, sum(grand_total) FROM orders
GROUP BY customer_name HAVING sum(grand_total) > 400;
As you can see above, HAVING clause is used with sum() aggregate function to filter the resultset with grand total more than $400.