ORACLE GROUP BY - Online Tech Support

How To Use Oracle Group By

online tech support Comments Off

This Online Tech Support tutorial page is based on examples. Clause Oracle GROUP BY removes duplicated lines and it works almost the same way as function Oracle DISTINCT. The only difference is that you are defining the columns that should be grouped manually in the GROUP BY section. Manually entered columns into Oracle GROUP BY clause gives your SQL query much better performance than it would be done using the DISTINCT function. We do suggest preferring to use Oracle GROUP BY clause to Oracle DISTINCT. The syntax of Oracle GROUP BY is following:

 SELECT <columns>
   FROM <tables>
GROUP BY <columns>;

To be able explain better the examples below we are going use the DUAL table with function Oracle CONNECT BY and that way we will get generate 10 rows. We did chose to write this way to perform the SQL queries without creating any new table and you will be able to try the sample just doing copy-paste into your database. The “source” query is following:

 SELECT rownum AS id, 'Online Tech Support' AS name
   FROM dual
CONNECT BY rownum < 11;

The output above shows the unique ID values and name values are the same over all rows. The second SQL query below is the first example with the GROUP BY and the clause is applied on the last “source” query above.

SELECT id, name
FROM ( SELECT rownum AS id, 'Online Tech Support' AS name
         FROM dual
      CONNECT BY rownum < 11 )
GROUP BY id, name;

As the output shows below the GROUP BY clause did not give any difference. The reason is in the ID values that are unique and GROUP BY removes only duplicated rows.

The second example will use the GROUP BY clause only on the name column and the output will be very different from the last SQL query.

SELECT name
FROM ( SELECT rownum AS id, 'Online Tech Support' AS name
         FROM dual
      CONNECT BY rownum < 11 )
GROUP BY name;

Now the GROUP BY returned only 1 name row because the name was not unique.

To know how many name rows we did have originally we can use Oracle COUNT function. We will add the new column to the end as the last column and it will be named “lines“.

SELECT name, COUNT(*) AS lines
FROM ( SELECT rownum AS id, 'Online Tech Support' AS name
         FROM dual
      CONNECT BY rownum < 11 )
GROUP BY name;

The output has one NAME and LINES row and the COUNT function returned value 10. Also take a look at the SQL query above – the COUNT function has NOT been added to the GROUP BY clause. This is written so because function COUNT is an aggregate function and it works as part of GROUP BY clause.

Important To Know:

The next example will describe the most common mistakes using the GROUP BY clause. The first mistake is a missing Oracle column from the Oracle GROUP BY declaration.

SELECT id, name
FROM ( SELECT rownum AS id, 'Online Tech Support' AS name
         FROM dual
      CONNECT BY rownum < 11 )
GROUP BY name;

Error ORA-00979: not a GROUP BY expression is raised when there is a difference between GROUP BY columns and SELECT columns. All columns that are declared in SELECT should be also added into the GROUP BY too. The only exception is using columns with Oracle aggregate functions and in that case you don’t have to declare them in the GROUP BY.

The following example is about the column meanings in Oracle SELECT statement. You need to understand what the purpose of the query output is. And to go through the next examples we need to use a new column named “revenue“. The new “source” SQL looks follow:

 SELECT rownum AS id, 'Online Tech Support' AS name, 10 AS revenue
   FROM dual
CONNECT BY rownum < 11;

The sample query above has the same value in the REVENUE column over all rows. The second “common” mistake is more logical than technical. Some developer may want to see the total revenue and accidently added the revenue column into Oracle GROUP BY part. The query output looks below:

SELECT name, revenue
FROM ( SELECT rownum AS id, 'Online Tech Support' AS name, 10 AS revenue
         FROM dual
      CONNECT BY rownum < 11 )
GROUP BY name, revenue;

They did expect to see revenue amount 100 (amount 10 per 10 rows), but this SELECT query returned only 10. The correct amount of revenue can be received using aggregate function Oracle SUM and removing the revenue column from the GROUP BY clause.

SELECT name, SUM(revenue) AS revenue
FROM ( SELECT rownum AS id, 'Online Tech Support' AS name, 10 AS revenue
         FROM dual
      CONNECT BY rownum < 11 )
GROUP BY name;

Please note that both Oracle SQL queries are correct to write and the only different of the SQLs depends on what would you like to see.

See Also:
Home
Oracle Select

How To Use Oracle HAVING Clause

online tech support Comments Off

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