How To Use Oracle GROUP BY
  Home
 

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement oracle ORA-00979: not a GROUP BY expression

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement oracle ORA-00979: not a GROUP BY expression

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement oracle ORA-00979: not a GROUP BY expression

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;

online tech support online computer help computer technician computer problems computer oracle group by oracle group by in oracle rownum in oracle rownum oracle sql oracle sql database sql oracle retirement planning retirement oracle ORA-00979: not a GROUP BY expression

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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*