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
Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s