Oracle SUM function

SUM function returns the sum of the expression. It is an example of aggregate functions.

Syntax:

select sum(column_name) from table_name;

SUM can be done with the numeric values only.

Examples:-

Consider below EMP table structure

EMPNNO EMPNAME SALARY DEPTNO
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2
19 Dora 8500 3
20 Samuel 6900 3
21 Mary 7500 3
22 Daniel 6500 4
23 Ricardo 7800 4
24 Mark 7200 4

1. Find the sum of salary for all employees.

select sum(salary) from EMP;
SUM(SALARY)
————————
      126200
2. Find the sum of salary of the employees in a department (Using where clause).
select sum(salary) from EMP where deptno = 1;
SUM(SALARY)
————————
     24100
3. SUM cannot be used with non-numeric columns
select sum(empname) from emp;
           *
ERROR at line 1:
ORA-01722: invalid number
4) SUM of salaries of employees in each (Using group by)
select deptno,sum(salary) from emp group by deptno;
 DEPTNO SUM(SALARY)
———-   ———–
1              24100
2              25700
5              32000
4              21500
3              22900
5) SUM with dual table.
select sum(3+5) from dual;
 SUM(3+5)
———-
8
←
→