How To Use Group By In Oracle

How To Use Group By In Oracle

December 12, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. Clause Oracle Group By will remove duplicated lines from the output and it works in the same principle as function Oracle Distinct. The only difference is that you are defining the columns to be grouped manually in Group By clause. When the columns are selected manually in Group By clause it will give your SQL statement much better performance than the automatic selection of Oracle Distinct function. Our Oracle DBA suggests you to prefer using Oracle Group By clause to Oracle Distinct. The Oracle group by syntax is following:

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

For the following Oracle DBA examples we will use table Dual with CONNECT BY function to generate 10 lines. This is necessary to perform the SQL queries without creating any table and you will be able to try the samples just doing copy-paste into your own database. The example’s “source” is following:

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

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online

In this first Oracle DBA example we will apply the Group By on the last 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;

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online

As you see in this output above the Oracle Group By didn’t give any difference from the “source” output. The reason is in the ID column’s value that are unique and unique values wouldn’t group. The next SQL statement will use the Group By clause on the name column only and you will see output will become different from the Group By statement above.

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

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online

Now the Group By in Oracle returned much less lines, actually it returned only 1 name line.

For next we will see how to get total amount of lines for every different name group. We will use Oracle Count function and our Oracle DBA added it to the end as the last column in the Select statement.

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

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online

Please note in the last Oracle DBA example above the Count function has not been added to the Oracle Group By clause. This is because function Count in Oracle is an aggregate function and it does work as Group By or as part of it.

Important To Know:

The following example will describe you the most common mistakes using the Group By in Oracle. The first is missing Oracle column in 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;

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online ORA-00979: not a GROUP BY expression personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Error ORA-00979: not a GROUP BY expression is raised because there is a difference in declared GROUP BY columns and SELECT columns. All columns that are declared in Oracle Select should be added into the Group By part too. The only exception is when you are using columns with Oracle aggregate functions.

The following example is about the column meaning in Oracle Select statement. You need to understand what do you want to receive from your Select statement. And to go through the next examples we need to use an extra Oracle column named revenue. The example “source” SQL is following:

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

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online ORA-00979: not a GROUP BY expression personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In the query above has exactly same revenue value for all lines and the second common mistake is to add the revenue column into Oracle Group By clause and this would not return the total revenue. See the result 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;

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online ORA-00979: not a GROUP BY expression personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

We did expect to see amount 100 as the total revenue for 10 lines, but this Oracle Select returned only number 10. To get the correct amount of revenue you need to use an aggregate function Oracle SUM and remove the revenue column from the Group By.

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;

dba oracle dba database management database management system oracle performance tuning oracle group by oracle group by in oracle online courses online ORA-00979: not a GROUP BY expression personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Please note that both Oracle SQL queries are correct to write and the way to write them depends on what would you want to see in output.

See Also:
Oracle Select Oracle NVL Oracle Decode Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*