Count distinct with group by : DISTINCT « Select Query « Oracle PL / SQL

1. Oracle PL / SQL
2. Select Query
3. DISTINCT

Count distinct with group by

 
SQL> create table offerings
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , Coder      NUMBER(4)
  5  , location   VARCHAR2(8)
  6  ) ;

Table created.

SQL> insert into offerings values ('SQL',date '2009-04-12',13,'DALLAS' );

1 row created.

SQL> insert into offerings values ('OAU',date '2009-08-10',4,'CHICAGO');

1 row created.

SQL> insert into offerings values ('SQL',date '2009-10-04',1,'SEATTLE');

1 row created.

SQL> insert into offerings values ('SQL',date '2009-12-13',1,'DALLAS' );

1 row created.

SQL> insert into offerings values ('JAV',date '2009-12-13',4,'SEATTLE');

1 row created.

SQL> insert into offerings values ('XML',date '2000-02-03',1,'DALLAS' );

1 row created.

SQL> insert into offerings values ('JAV',date '2000-02-01',11,'DALLAS' );

1 row created.

SQL> insert into offerings values ('PLS',date '2000-09-11',8,'DALLAS' );

1 row created.

SQL> insert into offerings values ('XML',date '2000-09-18',NULL,'SEATTLE');

1 row created.

SQL> insert into offerings values ('OAU',date '2000-09-27',13,'DALLAS' );

1 row created.

SQL> insert into offerings values ('ERM',date '2001-01-15',NULL, NULL    );

1 row created.

SQL> insert into offerings values ('PRO',date '2001-02-19',NULL,'DALLAS' );

1 row created.

SQL> insert into offerings values ('RSD',date '2001-02-24',8,'CHICAGO');

1 row created.

SQL>
SQL> select Coder
  2  ,      count(distinct course)
  3  ,      count(*)
  4  from   offerings
  5  group  by Coder;

     CODER COUNT(DISTINCTCOURSE)   COUNT(*)
---------- --------------------- ----------
         1                     2          3
         4                     2          2
         8                     2          2
        11                     1          1
        13                     2          2
                               3          3

6 rows selected.

SQL>
SQL>
SQL> drop table offerings;

Table dropped.

   
  

Related examples in the same category