Oracle SQL - Multiple-Column Grouping

Introduction

You can group on multiple-column expressions, separated by commas.

The following code produces an overview of the number of registrations per course.


select   r.course, r.begindate
,        count(r.attendee) as attendees
from     registrations r
group by r.course, r.begindate;

This result shows one row for each different (COURSE, BEGINDATE) combination found in the REGISTRATIONS table.

Demo

SQL>
SQL> drop table registrations;

Table dropped.-- from  w  ww. ja  va 2s.  c o  m

SQL> create table registrations(
  2  attendee    NUMBER(4)   not null,
  3  course    VARCHAR2(6) ,
  4  begindate   DATE      not null,
  5  evaluation  NUMBER(1)   check (evaluation in (1,2,3,4,5))) ;
SQL>
SQL> insert into registrations values (7002,'SQL',date '1999-04-12',4   );
SQL> insert into registrations values (7934,'SQL',date '1999-04-12',5   );
SQL> insert into registrations values (7006,'SQL',date '1999-04-12',4   );
SQL> insert into registrations values (7011,'SQL',date '1999-04-12',2   );
SQL> insert into registrations values (7008,'SQL',date '1999-10-04',NULL);
SQL> insert into registrations values (7009,'SQL',date '1999-10-04',3   );
SQL> insert into registrations values (7902,'SQL',date '1999-10-04',4   );
SQL> insert into registrations values (7902,'SQL',date '1999-12-13',NULL);
SQL> insert into registrations values (7006,'SQL',date '1999-12-13',NULL);
SQL> insert into registrations values (7003,'JSON',date '1999-08-10',4   );
SQL> insert into registrations values (7012,'JSON',date '1999-08-10',4   );
SQL> insert into registrations values (7902,'JSON',date '1999-08-10',5   );
SQL> insert into registrations values (7010,'JSON',date '2000-09-27',5   );
SQL> insert into registrations values (7002,'JAVA',date '1999-12-13',2   );
SQL> insert into registrations values (7007,'JAVA',date '1999-12-13',NULL   );
SQL> insert into registrations values (7011,'JAVA',date '1999-12-13',5   );
SQL> insert into registrations values (7008,'JAVA',date '1999-12-13',5   );
SQL> insert into registrations values (7009,'JAVA',date '1999-12-13',4   );
SQL> insert into registrations values (7004,'JAVA',date '2000-02-01',3   );
SQL> insert into registrations values (7008,'JAVA',date '2000-02-01',4   );
SQL> insert into registrations values (7006,'JAVA',date '2000-02-01',5   );
SQL> insert into registrations values (7012,'XML',date '2000-02-03',4   );
SQL> insert into registrations values (7002,'XML',date '2000-02-03',5   );
SQL> insert into registrations values (7004,'PLS',date '2000-09-11',NULL);
SQL> insert into registrations values (7002,'PLS',date '2000-09-11',NULL);
SQL> insert into registrations values (7011,'PLS',date '2000-09-11',NULL);
SQL>
SQL>
SQL> select   r.course, r.begindate
  2  ,        count(r.attendee) as attendees
  3  from     registrations r
  4  group by r.course, r.begindate;

COURSE | BEGINDATE  | ATTENDEES
------ | ---------- | ---------
JAVA   | 13-12-1999 |  00005.00
PLS    | 11-09-2000 |  00003.00
JSON   | 27-09-2000 |  00001.00
JSON   | 10-08-1999 |  00003.00
SQL    | 12-04-1999 |  00004.00
SQL    | 13-12-1999 |  00002.00
XML    | 03-02-2000 |  00002.00
JAVA   | 01-02-2000 |  00003.00
SQL    | 04-10-1999 |  00003.00

9 rows selected.

SQL>

Related Topic