Oracle PL/SQL/Aggregate Functions/SUM
Содержание
- 1 Add an "OTHER" and "TOTAL" column to the report:
- 2 Compute sum on salary
- 3 Doing calculation in sum() function
- 4 GROUP BY function would produce inventory totals distributed across different vendors
- 5 Sum column for a certain time period
- 6 Sum() function and having clause
- 7 SUM function and NULLs
- 8 Sum salary group by department number
- 9 Sum salary over
- 10 SUM: total for all NOT NULL values, accepts only numeric datatype values
- 11 sum with column calculation
- 12 Syntax: SUM([DISTINCT]|[ALL] <numeric_expression>)
- 13 Using the SUM function with GROUP BY Clause
- 14 Wrap case when into sum() function
Add an "OTHER" and "TOTAL" column to the report:
<source lang="sql">
SQL> SQL> SQL> create table customer(
2 cust_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 );
Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");
1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");
1 row created. SQL> SQL> select state from customer; ST -- CA NY CA NY CA NY NY CA MO NY NJ NJ RI NY 14 rows selected. SQL> SQL> SELECT
2 SUM( decode ( state, "NJ" , 1 , 0) ) as nj,
3 SUM( decode ( state, "NY" , 1 , 0) ) as ny,
4 SUM( decode ( state, "RI" , 1 , 0) ) as ri,
5 SUM( decode ( state, "NJ", 0,
6 "NY", 0,
7 "RI", 0,
8 1) ) AS other,
9 COUNT(*) AS total
10 FROM customer;
NJ NY RI OTHER TOTAL
---------- ---------- ---------- ----------
2 6 1 5 14
1 row selected. SQL> SQL> drop table customer; Table dropped. SQL> SQL> --
</source>
Compute sum on salary
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> set pause off SQL> break on deptno skip page - > on job SQL> SQL> compute sum label total of sal on deptno SQL> compute count number of comm on deptno SQL> SQL> select deptno, job, empno, ename, sal, comm
2 from emp 3 order by deptno, job;
DEPTNO JOB EMPNO ENAME SAL COMM
-------- ------ -------- ------ ------
10 Designer 7 Chris 2450 [N/A]
9 Peter 5000 [N/A]
Manager 14 Mike 1300 [N/A]
- ******** ------ ------
count 0 number 3 total 8750 DEPTNO JOB EMPNO ENAME SAL COMM
-------- ------ -------- ------ ------
20 Designer 4 Jane 2975 [N/A]
TRAINER 8 Smart 3000 [N/A]
11 Ana 1100 [N/A]
13 Fake 3000 [N/A]
1 Tom 800 [N/A]
- ******** ------ ------
count 0 number 5 total 10875 DEPTNO JOB EMPNO ENAME SAL COMM
-------- ------ -------- ------ ------
30 Designer 6 Black 2850 [N/A]
Manager 12 Jane 800 [N/A]
Tester 10 Take 1500 0
2 Jack 1600 300
3 Wil 1250 500
5 Mary 1250 1400
- ******** ------ ------
count 4 number 6 total 9250 14 rows selected. SQL> SQL> compute COMPUTE sum LABEL "sum" OF bytes ON pool COMPUTE sum LABEL "total" OF sal ON deptno COMPUTE count LABEL "count" number LABEL "number" OF comm ON deptno SQL> clear computes computes cleared SQL> clear breaks breaks cleared SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL>
</source>
Doing calculation in sum() function
<source lang="sql">
SQL> SQL> SQL> create table sale(
2 gift_id integer 3 ,product_id integer 4 ,quantity number(4,0) 5 ,price number(7,2) 6 ,primary key (gift_id ,product_id) 7 );
Table created. SQL> SQL> -- order_item table inserts SQL> insert into sale(gift_id, product_id, quantity, price) values(1, 2, 10, 23.00 ); 1 row created. SQL> insert into sale(gift_id, product_id, quantity, price) values(2, 1, 1, 23.11 ); 1 row created. SQL> SQL> SQL> select product_id, sum(quantity) "TOTAL SOLD",
2 sum(price * quantity) "GROSS $" 3 from sale 4 group by product_id;
PRODUCT_ID TOTAL SOLD GROSS $
---------- ----------
1 1 23.11
2 10 230
2 rows selected. SQL> SQL> SQL> drop table sale; Table dropped.
</source>
GROUP BY function would produce inventory totals distributed across different vendors
<source lang="sql">
SQL> SQL> CREATE TABLE MyProduct
2 ( 3 MAKER VARCHAR (25), 4 MODEL VARCHAR (25), 5 PRICE NUMERIC 6 );
Table created. SQL> SQL> INSERT INTO MyProduct VALUES("CHRYSLER","CROSSFIRE",33620); 1 row created. SQL> INSERT INTO MyProduct VALUES("CHRYSLER","300M",29185); 1 row created. SQL> INSERT INTO MyProduct VALUES("HONDA","CIVIC",15610); 1 row created. SQL> INSERT INTO MyProduct VALUES("HONDA","ACCORD",19300); 1 row created. SQL> SQL> INSERT INTO MyProduct VALUES("FORD","MUSTANG",15610); 1 row created. SQL> INSERT INTO MyProduct VALUES("FORD","LATESTnGREATEST",NULL); 1 row created. SQL> INSERT INTO MyProduct VALUES("FORD","FOCUS",13005); 1 row created. SQL> SQL> SQL> SELECT
2 Maker, 3 SUM(price) total 4 FROM MyProduct 5 GROUP BY maker;
MAKER TOTAL
--------
HONDA ######## FORD ######## CHRYSLER ######## SQL> SQL> drop table MyProduct; Table dropped. SQL>
</source>
Sum column for a certain time period
<source lang="sql">
SQL> CREATE TABLE sales(
2 product_id NUMBER(6), 3 cid NUMBER, 4 time_id DATE, 5 channel_id CHAR(1), 6 promo_id NUMBER(6), 7 sold NUMBER(3), 8 amount NUMBER(10,2), 9 cost NUMBER(10,2) 10 );
Table created. SQL> SQL> select sum(amount)
2 from sales
3 where time_id between to_date("2006-02-01", "YYYY-MM-DD") and to_date("2006-02-28", "YYYY-MM-DD");
SUM(AMOUNT)
1 row selected. SQL> SQL> drop table sales; Table dropped.
</source>
Sum() function and having clause
<source lang="sql">
SQL> SQL> CREATE TABLE server_usage (
2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 );
Table created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created. SQL> SQL> SET ECHO ON SQL> SELECT emp_id, pro_id
2 FROM server_usage
3 WHERE pro_id = 1001 OR pro_id=1002
4 GROUP BY emp_id, pro_id
5 HAVING SUM(hours_logged) > 20;
101 1001
102 1002
106 1001
107 1002
4 rows selected. SQL> SQL> SQL> drop table server_usage; Table dropped.
</source>
SUM function and NULLs
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), NULL ,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> --SUM function and NULLs SQL> SQL> SELECT SUM(Salary) as AVG_SAL FROM Employee;
AVG_SAL
28251.24
SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Sum salary group by department number
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select deptno
2 , sum(sal) 3 from emp 4 group by deptno; DEPTNO SUM(SAL)
----------
10 8750
20 10875
30 9250
SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Sum salary over
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select mgr, ename, sal
2 , sum(sal) over
3 ( order by mgr, sal, EMPNO
4 range unbounded preceding
5 ) as cumulative
6 from emp
7 order by mgr, sal;
MGR ENAME SAL CUMULATIVE
-------- ---------- ----------
4 Smart 3000 3000
4 Fake 3000 6000
6 Jane 800 6800
6 Wil 1250 8050
6 Mary 1250 9300
6 Take 1500 10800
6 Jack 1600 12400
7 Mike 1300 13700
8 Ana 1100 14800
9 Chris 2450 17250
9 Black 2850 20100
9 Jane 2975 23075
13 Tom 800 23875
Peter 5000 28875
14 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
SUM: total for all NOT NULL values, accepts only numeric datatype values
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> --SUM: total for all NOT NULL values SQL> --accepts only numeric datatype values SQL> SQL> select SUM(Salary) from Employee; SUM(SALARY)
32574.02
SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
sum with column calculation
<source lang="sql">
SQL> SQL> create table employee(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(3) 14 ,birthdate date 15 ,hiredate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created. SQL> SQL> select sum(salary) "TOTAL Salary",
2 sum(salary * 12) "GROSS $" 3 from employee 4 group by city;
TOTAL Salary GROSS $
----------
4 48
47 564
10 120
12 144
9 108
12 144
8 96
13 156
8 rows selected. SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL> --
</source>
Syntax: SUM([DISTINCT]|[ALL] <numeric_expression>)
<source lang="sql">
SQL> SQL> CREATE TABLE MyProduct
2 ( 3 MAKER VARCHAR (25), 4 MODEL VARCHAR (25), 5 PRICE NUMERIC 6 );
Table created. SQL> SQL> INSERT INTO MyProduct VALUES("CHRYSLER","CROSSFIRE",33620); 1 row created. SQL> INSERT INTO MyProduct VALUES("CHRYSLER","300M",29185); 1 row created. SQL> INSERT INTO MyProduct VALUES("HONDA","CIVIC",15610); 1 row created. SQL> INSERT INTO MyProduct VALUES("HONDA","ACCORD",19300); 1 row created. SQL> SQL> INSERT INTO MyProduct VALUES("FORD","MUSTANG",15610); 1 row created. SQL> INSERT INTO MyProduct VALUES("FORD","LATESTnGREATEST",NULL); 1 row created. SQL> INSERT INTO MyProduct VALUES("FORD","FOCUS",13005); 1 row created. SQL> SQL> SELECT
2 SUM(price) total 3 FROM MyProduct; TOTAL
SQL> drop table MyProduct; Table dropped. SQL> SQL>
</source>
Using the SUM function with GROUP BY Clause
<source lang="sql">
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SQL> -- Using the SUM function with GROUP BY Clause SQL> SQL> SELECT job, SUM(sal) as "Total sales"
2 FROM emp 3 GROUP BY job;
JOB Total sales
-----------
CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 6000 SQL> SQL> drop table emp; SQL> SQL> SQL> SQL>
</source>
Wrap case when into sum() function
<source lang="sql">
SQL> SQL> create table emp(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(5,2) 14 ,birthdate date 15 ,startDate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
2 (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");
1 row created. SQL> SQL> SQL> select
2 sum ( case when salary between 6 and 10 then 1 else 0 end ) 3 as sal_6_10, 4 sum ( case when salary between 11 and 15 then 1 else 0 end ) 5 as sal_11_15, 6 sum ( case when salary between 16 and 20 then 1 else 0 end ) 7 as sal_16_20, 8 sum ( case when salary between 21 and 25 then 1 else 0 end ) 9 as sal_21_25 10 from emp; SAL_6_10 SAL_11_15 SAL_16_20 SAL_21_25
---------- ---------- ----------
0 0 0 0
1 row selected. SQL> drop table emp; Table dropped.
</source>
