Oracle SUM Function

Oracle SUM function returns the sum of all values in the specified column. Specified column should have numeric data type, SUM function does not accept columns having a data type other than numeric, such as character or date.

If you want to sum only the unique values that show up within a table, you would use SUM function in conjunction with DISTINCT.

The syntax of the SUM function is as follows:

SELECT SUM(<column-name>) FROM TABLE_NAME;

Oracle SUM Function Examples

Creating table for demonstrating SUM function

CREATE TABLE EMP_SALARY_COMMISION

(EMP_ID NUMBER(4) ,

SALARY NUMBER(7,2),

COMM NUMBER(7,2)

);

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('100', '1000', '110');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('101', '1000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('102', '1000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('103', '1000', '20');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('104', '2000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('105', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('106', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('107', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('108', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('109', '3000');

COMMIT;

Sum of all rows

SELECT SUM(COMM) FROM EMP_SALARY_COMMISION --------- 450

Sum of distinct rows

SELECT SUM(DISTINCT COMM) FROM EMP_SALARY_COMMISION --------- 290

Adding alias to sum value

SELECT SUM(SALARY) AS TOTAL_SALARY FROM EMP_SALARY_COMMISION;

All Chapters