Oracle GROUP BY Clause

The GROUP BY clause in the Oracle database is used to arrange the similar data item into set of logical groups. It is used only in the SELECT statement.

Oracle GROUP BY clause Syntax:-

SELECT expressions
FROM tablename
[ WHERE condition ]
GROUP BY expressions;

The GROUP BY clause is always used after the FROM clause, but if WHERE clause is also there then it is used after the WHERE clause.

The GROUP BY columnName will arrange similar data items based on the given column. Now, let’s understand it through the pre-defined EMP table given in the Oracle database.

SQL> SELECT empno, job, sal, hiredate, deptno FROM emp;

     EMPNO JOB              SAL HIREDATE      DEPTNO
---------- --------- ---------- --------- ----------
      7369 CLERK            800 17-DEC-80         20
      7499 SALESMAN        1600 20-FEB-81         30
      7521 SALESMAN        1250 22-FEB-81         30
      7566 MANAGER         2975 02-APR-81         20
      7654 SALESMAN        1250 28-SEP-81         30
      7698 MANAGER         2850 01-MAY-81         30
      7782 MANAGER         2450 09-JUN-81         10
      7788 ANALYST         3000 19-APR-87         20
      7839 PRESIDENT       5000 17-NOV-81         10
      7844 SALESMAN        1500 08-SEP-81         30
      7876 CLERK           1100 23-MAY-87         20
      7900 CLERK            950 03-DEC-81         30
      7902 ANALYST         3000 03-DEC-81         20
      7934 CLERK           1300 23-JAN-82         10

14 rows selected.

In the EMP table, employees can be grouped based on the job they are doing like clerk, manager, analyst. Similarly, they can be grouped based on the department number.

Oracle GROUP BY Examples

Example1) Write a Query to display number of employess in every department (deptno wise) from emp table using GROUP BY clause.

SQL> SELECT deptno, COUNT(*)
     FROM emp
     GROUP BY deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

From emp table, the deptno 30, 20, and 10 having 6, 5, and 3 employess respectively.

Example2) Write a Oracle SQL query to display number of employess in each job from emp table using GROUP BY clause.

SQL> SELECT job, COUNT(*)
     FROM emp
     GROUP BY job;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2

In these examples, the heading of second column is COUNT(*) which is the number of employess belongs to those deptno/job. If we want to display our own heading then place the heading with double quote just after the COUNT(*) in SELECT statement. Example:-

SQL> SELECT job, COUNT(*) "Number of Employees"
     FROM emp
     GROUP BY job;

JOB       Number of Employees
--------- -------------------
CLERK                       4
SALESMAN                    4
PRESIDENT                   1
MANAGER                     3
ANALYST                     2

We can also use alias name for it,

SQL> SELECT job, COUNT(*) AS Employees
     FROM emp
     GROUP BY job;

Example3) Write a query to display the maximum and minimum salary (sal) in every dept using GROUP BY clause.

SQL> SELECT deptno, MAX(sal), MIN(sal)
     FROM emp
     GROUP BY deptno;

    DEPTNO   MAX(SAL)   MIN(SAL)
---------- ---------- ----------
        30       2850        950
        20       3000        800
        10       5000       1300

Example4) Write a query to display year and the number of employees hired in that year from emp table using GROUP BY clause.

SQL> SELECT TO_CHAR(hiredate, 'YYYY'), COUNT(*)
     FROM emp
     GROUP BY TO_CHAR(hiredate, 'YYYY');

TO_C   COUNT(*)
---- ----------
1987          2
1980          1
1982          1
1981         10

In all above examples we were using group functions but we can also use GROUP BY clause without group functions. Example:-

SQL> SELECT deptno
     FROM emp
     GROUP BY deptno;

    DEPTNO
----------
        30
        20
        10

Oracle GROUP BY Multiple Columns

If we are using other then group functions columns specified after SELECT, then those columns must be used after the GROUP BY clause otherwise Oracle server returns an error not a group by expression. Example:-

SQL> SELECT deptno, SUM(sal), job
     FROM emp
     GROUP BY deptno;

select deptno, sum(sal), job
                         *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL> SELECT deptno, SUM(sal), job
     FROM emp
     GROUP BY job;

select deptno, sum(sal), job
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Solution:-

SQL> SELECT deptno, SUM(sal), job
     FROM emp
     GROUP BY deptno, job;

    DEPTNO   SUM(SAL) JOB
---------- ---------- ---------
        20       1900 CLERK
        30       5600 SALESMAN
        20       2975 MANAGER
        30        950 CLERK
        10       5000 PRESIDENT
        30       2850 MANAGER
        10       1300 CLERK
        10       2450 MANAGER
        20       6000 ANALYST

9 rows selected.

Whenever we are submitting GROUP BY clause then database server arrange data in same proof based on specified columns after GROUP BY clause. These results will be stored internally in resultset table. And from this resultset table we are selecting displayed columns other select list. That’s why after group by clause we are specifying number of columns also. These all columns not required to display after select clause. Example:-

SQL> SELECT deptno
     FROM emp
     GROUP BY deptno, job;

    DEPTNO
----------
        20
        30
        20
        30
        10
        30
        10
        10
        20

9 rows selected.

If we try to display group function with another columns then database server returns error. To overcome this problem we must use GROUP BY clause. Example:-

SQL> SELECT AVG(sal) FROM emp;
  AVG(SAL)
----------
2073.21429

SQL> SELECT job, AVG(sal) FROM emp;
ORA-00937: not a single-group group function

Since AVG() is a group by function so we can’t use it with another column without using GROUP BY clause.

SQL> SELECT job, AVG(sal) 
     FROM emp 
     GROUP BY job;

JOB         AVG(SAL)
--------- ----------
CLERK         1037.5
SALESMAN        1400
PRESIDENT       5000
MANAGER   2758.33333
ANALYST         3000

SQL> SELECT deptno, SUM(sal)
     FROM emp
     GROUP BY deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

When we are using group function then Oracle database server executes all values at a time for the table column but when we are submitting group function within group by clause then those group functions are executes for each and every sub groups within group by.

If we want to restrict the group then we are using HAVING clause.

ROLLUP and CUBE

Oracle 8i introduced ROLLUP, CUBE clause which are only used along with GROUP BY clause. These clauses are used to calculate subtotal, grand total.

The syntax for ROLLUP:-
SELECT expression1, ..., expressionN
FROM tableName
GROUP BY ROLLUP (expression1, ..., expressionN);

The syntax for CUBE:-
SELECT expression1, ..., expressionN
FROM tableName
GROUP BY CUBE (expression1, ..., expressionN);

• ROLLUP is used to calculate the sum of total values based on a single column.
• CUBE is used to calculate the sum of the total, grand total based on number of column.

Examples of cube and rollup used with group by clause,

Example1:-

SQL> SELECT deptno, job, sum(sal)
     FROM emp
     GROUP BY ROLLUP(deptno, job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 CLERK           1900
        20 ANALYST         6000
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025

13 rows selected.

Example2:-

SQL> SELECT deptno, job, sum(sal), count(*)
     FROM emp
     GROUP BY CUBE(deptno, job);

    DEPTNO JOB         SUM(SAL)   COUNT(*)
---------- --------- ---------- ----------
                          29025         14
           CLERK           4150          4
           ANALYST         6000          2
           MANAGER         8275          3
           SALESMAN        5600          4
           PRESIDENT       5000          1
        10                 8750          3
        10 CLERK           1300          1
        10 MANAGER         2450          1
        10 PRESIDENT       5000          1
        20                10875          5
        20 CLERK           1900          2
        20 ANALYST         6000          2
        20 MANAGER         2975          1
        30                 9400          6
        30 CLERK            950          1
        30 MANAGER         2850          1
        30 SALESMAN        5600          4

18 rows selected.

If we want to display ename and salary with total salary then we should use below query.

SQL> SELECT ename, sum(sal)
     FROM emp
     GROUP BY ROLLUP(ename);

ENAME        SUM(SAL)
---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JONES            2975
KING             5000
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250
                29025

15 rows selected.

The complete syntax for the select query is,

SELECT expressions
FROM tableName
WHERE condition
GROUP BY expressions
HAVING having_condition
ORDER BY columnName [ASC / DESC];

Also Learn:- Order by clause in Oracle database

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search