In Oracle, nulls in calculations involving aggregate functions are ignored : Aggregate Functions Basics « Aggregate Functions « Oracle PL / SQL






In Oracle, nulls in calculations involving aggregate functions are ignored



SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       48000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000,       NULL,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      36000,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000,       NULL,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       85000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       NULL,       'E')
  3  /

1 row created.

SQL>
SQL> select * from employee;

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000             E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000             E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000             E

7 rows selected.

SQL>
SQL> -- In Oracle, nulls in calculations involving aggregate functions are ignored
SQL>
SQL> SELECT ename, curr_salary
  2  FROM employee
  3  UNION
  4  SELECT 'The average .......', average
  5  FROM
  6    (SELECT avg(curr_salary) average
  7    FROM employee);

ENAME               CURR_SALARY
------------------- -----------
Alison                    48000
Celia
David                     85000
James                     32000
Jode
Linda
Robert                    36000
The average .......       50250

8 rows selected.

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
SQL>
           
       








Related examples in the same category

1.Use the aggregate functions with any valid expression
2.Order by the result of aggregate function
3.Limit the rows passed to the aggregate function using a WHERE clause
4.Combine the max and avg function together
5.Retrieves the city and average salary of the employees grouped by city
6.Having with aggregate function
7.Aggregate function and alias column name