SQL HAVING clause
has average rating
8
out of 10.
Total 23 users rated.
Description
SQL HAVING clause specifies a search condition for a group or an aggregate. HAVING is usually used in a GROUP BY clause, but even if you are not using GROUP BY clause, you can use HAVING to function like a WHERE clause.You must use HAVING with SQL SELECT.
Syntax
WHERE <condition>
GROUP BY <columns>
[HAVING] <condition>;
Parameters
| Name | Description |
|---|---|
| table_name | Name of the table. |
| column_list | Name of the columns of the table. |
| columns | Name of the columns which will participate in grouping. |
How a HAVING clause works
- The select clause specifies the columns.
- The from clause supplies a set of potential rows for the result.
- The where clause gives a filter for these potential rows.
- The group by clause divide the rows in a table into smaller groups.
- The having clause gives a filter for these group rows.
Example :
The following query display cust_country and number of customer for a same grade for each cust_country, with following condition -
1. number of customer for a same 'grade' must be more than 2,
Sample table : customer
SELECT cust_country,COUNT(grade) FROM customer GROUP BY cust_country HAVING COUNT(grade)>2;
Output
SQL HAVING using where
In the following example, the SQL WHERE clause along with the HAVING clause have used to make a select statement.
Example
Sample table : customer
To get list of cust_city, sum of opening_amt, average of receive_amt and maximum payment_amt from customer table with following conditions-
1. grade of customer table must be 2,
2. average of receive_amt for each group of cust_city must be more than 500,
then, the following sql statement can be used :
SELECT cust_city,SUM(opening_amt),
AVG(receive_amt),MAX(payment_amt)
FROM customer
WHERE grade=2
GROUP BY cust_city
HAVING AVG(receive_amt)>500;
Output
SQL HAVING with order by
In the following example, the SQL WHERE clause along with the HAVING clause is used to make a query. An ORDER BY clause arranges the final result in the specific order. Default order is ascending.
Example
Sample table : customer
To get list of cust_city, sum of opening_amt, average of receive_amt and maximum payment_amt from customer table with following conditions-
1. grade of customer table must be 2,
2. average of receive_amt for each group of cust_city must be more than 500,
3. the output should be arranged in the ascending order of SUM(opening_amt),
then, the following sql statement can be used :
SELECT cust_city,SUM(opening_amt),
AVG(receive_amt),MAX(payment_amt)
FROM customer
WHERE grade=2
GROUP BY cust_city
HAVING AVG(receive_amt)>500
ORDER BY SUM(opening_amt);
Output
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
