Oracle Aggregate Function - Oracle/PLSQL SUM Function

• « Previous
• Next »

This Oracle tutorial explains how to use the Oracle/PLSQL SUM function.

SUM(x) adds all the values in x and returns the total.

SUM function operates on a group of rows and return one row of output. Null values are ignored by SUM function. You can use the DISTINCT keyword to exclude duplicate entries.

Syntax

The syntax for the Oracle/PLSQL SUM function is:

SELECT SUM( expression )
 FROM tables
 WHERE conditions;

expression can be a numeric field or formula.

The following SQL calculates the total salaries for all employees whose salary is more than 50000.

SELECT SUM(salary) AS "Total Salary"
 FROM employees
 WHERE salary > 50000;

We can use the DISTINCT clause within the SUM function. The SQL statement below returns the total salary of unique salary values where the salary is above $50,000 / year.

SELECT SUM(DISTINCT salary) AS "Total Salary"
 FROM employees
 WHERE salary > 50000;

If there were two salaries of $80,000/year, only one of these values would be used in the SUM function.

We can also use the expression in the SUM function.

SELECT SUM(sales * 0.05) AS "Total Commission"
 FROM orders;

We can also use the SUM function with a group by clause. In this way the SUM will calcuate the sum value for each group.

SELECT department, SUM(sales) AS "Total sales"
 FROM order_details
 GROUP BY department;

Example

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from  w w  w. j a  v  a 2  s.  c  o  m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> select sum(sal) from emp;

  SUM(SAL)
----------
     22575

SQL>

Using the SUM function with the group by clause:

SQL> select deptno, SUM(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       8450
        20       8275
        10       5850

SQL>

• Next »
• « Previous