How To Use HAVING In Oracle SQL

This help is based on examples so it would be easier to understand. Oracle Having Clause allows to add filters on sets returned by Group By clause. Oracle Having clause is working the same way as the WHERE clause only it applies on groups and sets. The syntax of Oracle Having clause is:

SELECT <columns>
   FROM <TABLES>
GROUP BY <COLUMNS>
HAVING <your_conditition(s)>;

To go through the following Having clause examples we will need some data and the easiest way without creating any table is to use a query and the Oracle Dual table. The Dual table exists in all Oracle database versions and you can just do copy-paste the example code and run it on your Oracle database. The following output shows the data we are going to use in the 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 courser 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 to return only groups who’s 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 one 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:
Oracle Select Oracle Rownum Oracle Group By Home