Oracle SQL - COUNT(*) Function

  1. HOME
  2. Oracle SQL
  3. Select
  4. Group Functions

Introduction

Besides column names, you can specify the asterisk (*) as an argument to the COUNT function.

COUNT(*) returns the number of rows in the entire group.

The following code shows another example of using the COUNT(*) function applied against the EMPLOYEES table.

In the First Attempt, 

select e.deptno, count(*)
from   emp e
group  by e.deptno;

Department 40 is missing in this result.

Then we can add an outer join in order to show department 40 as well.

Count Employees Per Department (Second Attempt) 

select deptno, count(*)
from   emp e
       right outer join
       departments d
       using (deptno)
group  by deptno;

The query is still wrong:we have one employee working for department 40.

To filter the null record, include the column name in COUNT function. 

select deptno, count(e.empno)
from   emp e
       right outer join
       departments d
       using (deptno)
group  by deptno;

Demo

SQL>
SQL>-- ww  w.j a  va  2 s  . c  o m
SQL> drop table emp;

Table dropped.

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>
SQL> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL>
SQL> select e.deptno, count(*)
  2  from   emp e
  3  group  by e.deptno;

   DEPTNO |  COUNT(*)
--------- | ---------
    30    |  00004.00
    2     |  00005.00
    1     |  00005.00

SQL>
SQL> select deptno, count(*)
  2  from   emp e
  3         right outer join
  4         departments d
  5         using (deptno)
  6  group  by deptno;

   DEPTNO |  COUNT(*)
--------- | ---------
    1     |  00005.00
    2     |  00005.00
    30    |  00004.00
    4     |  00001.00

SQL>
SQL> select deptno, count(e.empno)
  2  from   emp e
  3         right outer join
  4         departments d
  5         using (deptno)
  6  group  by deptno;

   DEPTNO | COUNT(E.EMPNO)
--------- | --------------
    1     |       00005.00
    2     |       00005.00
    30    |       00004.00
    4     |       00000.00

SQL>

Related Topic