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
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
———- ———–
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
———-
8
Advertisements
