Oracle SQL - Group Functions and Duplicate Values, DISTINCT

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

Introduction

To make SQL group functions to ignore duplicate values, except one, specify the keyword DISTINCT after the first parenthesis.

Although it is syntactically correct, the addition of DISTINCT is meaningless for the MIN and MAX functions. 

select count(deptno), count(distinct deptno)
,      avg(comm),  avg(coalesce(comm,0))
from   emp;

You can use group functions in the SELECT clause of a query without a GROUP BY clause.

The absence of a GROUP BY clause with group functions in the SELECT clause results in a single-row result.

The full table is aggregated into a single row.

You can achieve precisely the same result by grouping on a constant expression.

Demo

SQL>
SQL>-- from  w  ww. j  a  va2s .com
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> select count(deptno), count(distinct deptno)
  2  ,      avg(comm),  avg(coalesce(comm,0))
  3  from   emp;

COUNT(DEPTNO) | COUNT(DISTINCTDEPTNO) | AVG(COMM) | AVG(COALESCE(COMM,0))
------------- | --------------------- | --------- | ---------------------
     00014.00 |              00003.00 |  00550.00 |              00157.14

SQL>

Related Topic