This tutorial is based on examples to be easier to follow. The Oracle HAVING Clause allows adding more filters to the GROUP BY clause sets. The Oracle Having clause works in the same way as the WHERE clause only it applies to groups or sets. The Oracle Having clause syntax is following:
SELECT <columns> FROM <TABLES> GROUP BY <COLUMNS> HAVING <your_conditition(s)>;
To go through the examples with the Having clause we will need some data and the simplest way without creating any table is to use a query with the Oracle Dual table. Table Dual does exist in all Oracle database versions and you can just do copy-paste and run the example on your Oracle database. The following output shows the data we are going to use in examples below.
SELECT rownum AS id, 'Oracle database' AS name FROM DUAL CONNECT BY rownum < 11;
The first example how we will restrict the query by having only IDs greater than “7” and the query returns three lines. Take a look at the Having clause it sits below the Group By clause and this way written query the best practice. Since Oracle is quite flexible and you can write having above the Group By clause too and it will be treated in the same way since it applies to groups. In short, you can add the Having clause above or below the Group By clause but the best practice is to add below since it will be easier to follow and the clause works with sets returned by Group By clause.
SELECT id, NAME
FROM ( SELECT rownum AS id, 'Oracle database' AS name
FROM dual
CONNECT BY ROWNUM < 11 )
GROUP BY ID, NAME
HAVING id > 7;
The output above show only IDs greater than 7 which is we did expect. The second example demonstrates how the Having clause can be used with string operations. We will look for a name starting with letter “a” and the clause is below the Group By again.
SELECT name
FROM ( SELECT rownum AS id, 'Oracle database' AS name
FROM dual
CONNECT BY ROWNUM < 11 )
GROUP BY NAME
HAVING (NAME like 'a%');
The SQL query didn’t return anything and the reason is there isn’t any string starting with the a-letter. All name values are “Oracle database” texts. We will try the same condition again only this time with letter “O“.
SELECT name
FROM ( SELECT rownum AS id, 'Oracle database' AS name
FROM dual
CONNECT BY ROWNUM < 11 )
GROUP BY NAME
HAVING (NAME like 'O%');
The Oracle SQL query returned the Oracle database string and that does mean Oracle Having clause works like the WHERE clause. Of course this only an example and in real case we do recommend to write the ” NAME LIKE ‘O%’ ” condition into the WHERE clause to gain better performance to your SQL. When you set the restrictions more “deep” into your query and that leaves less data to group then your SQL query runs faster.
The following example shows that you can use aggregate functions like SUM, MIN, MAX, AVG, COUNT,… directly in the Having clause and they can’t be use in to the WHERE clause. The condition allows returning only groups whose COUNT is greater than zero.
SELECT name, COUNT(*) AS lines
FROM ( SELECT rownum AS id, 'Oracle database' AS name
FROM dual
CONNECT BY ROWNUM < 11 )
GROUP BY NAME
HAVING COUNT(*) > 1;
The 10 rows on the first SQL query are grouped to 1 line and the Select statement returned the set as one line. Now to try how works the Oracle COUNT function we will set the restriction to greater than “11“.
SELECT name, COUNT(*) AS lines
FROM ( SELECT rownum AS id, 'Oracle database' AS name
FROM dual
CONNECT BY ROWNUM < 11 )
GROUP BY NAME
HAVING COUNT(*) > 11;
The next query is almost the same as the first one only with additional column “REVENUE” and that is needed to write the next example a bit more complicated than the last ones.
SELECT rownum AS id, 'Oracle database' AS name, 10 AS revenue FROM DUAL CONNECT BY rownum < 11;
This example below shows how to write Oracle Having clause with more than one condition. You can use AND-s or OR-s with the conditions as the following is looking for lines with SUM(REVENUE) is 100 and name is starting with “O“.
SELECT name, SUM(revenue) AS revenue
FROM ( SELECT rownum AS id, 'Oracle database' AS name, 10 AS revenue
FROM dual
CONNECT BY ROWNUM < 11 )
GROUP BY NAME
HAVING name like 'O%' and SUM(revenue) = 100;
The query returned the row as the conditions for 10 lines in total are true.
See Also:
Home Oracle Select Oracle Group By