Oracle SQL - Grouping on Additional Columns

  1. HOME
  2. Oracle SQL
  3. Select
  4. HAVING Clause

Introduction

You sometimes need to group on additional columns.

For example, to see the employee number and the employee name, and the total number of course registrations.

The following query is trying to solve this kind of problem, produces an Oracle error message. 

select   e.empno, e.ename, count(*)
from     emp e
         join
         registrations r
         on (e.empno = r.attendee)
group by e.empno;
select   e.empno, e.ename, count(*)
                              *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

The following code shows another attempt which ended up with Error Message: Not a Single-Group Group Function 

select deptno
,      sum(msal)
from   emp;

select deptno
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Without a GROUP BY clause, the SUM function would return a single row, while DEPTNO would produce 14 department numbers.

Two columns with different row counts cannot be presented side-by-side in a single result.

Here is the correct script. 

select   deptno
,        sum(msal)
from     emp
group by deptno;

Summary

If your query contains a GROUP BY clause), the SELECT clause is allowed to contain only group expressions.

A group expression is a column name that is part of the GROUP BY clause, or a group function applied to any other column expression.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w w  w .  java 2  s  . c  om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL> select   deptno
  2  ,        sum(msal)
  3  from     emp
  4  group by deptno;

   DEPTNO | SUM(MSAL)
--------- | ---------
    30    |  12350.00
    2     |  11875.00
    1     |  12850.00

SQL>

Related Topic