Oracle SQL - Select Group Functions

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

Introduction

COUNT is an example of a group function . All group functions have two important properties in common:

  • They can be applied only to sets of values.
  • They return a single aggregated value, derived from that set of values.

The group functions often occur in combination with GROUP BY and optionally the HAVING clause in SQL commands.

The most important Oracle group functions are listed in the following table.

Function Description Applicable To
COUNT() Number of valuesAll data types
SUM() Sum of all values Numeric data
MIN() Minimum value All data types
MAX() Maximum value All data types
AVG() Average value Numeric data
MEDIAN() Median (middle value) Numeric or date (time) data
STATS_MODE() Modus (most frequent value) All data types
STDDEV() Standard deviation Numeric data
VARIANCE()Statistical varianceNumeric data

The functions MIN and MAX are applicable to any data type, including dates and alphanumeric strings.

MIN and MAX need only an ordering (sorting) criterion for the set of values.

You can apply the AVG function only to numbers, because the average is defined as the SUM divided by the COUNT, and the SUM function accepts only numeric data.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  w w. j  av  a  2s  . c  o  m

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 e.deptno
  2  ,      count(e.job)
  3  ,      sum(e.comm)
  4  ,      avg(e.msal)
  5  ,      median(e.msal)
  6  from   emp e
  7  group  by e.deptno;

   DEPTNO | COUNT(E.JOB) | SUM(E.COMM) | AVG(E.MSAL) | MEDIAN(E.MSAL)
--------- | ------------ | ----------- | ----------- | --------------
    1     |     00005.00 |    00500.00 |    02570.00 |       02450.00
    2     |     00005.00 |  [N/A]      |    02375.00 |       02975.00
    30    |     00004.00 |    01700.00 |    03087.50 |       01550.00

SQL>

Related Topics