Aggregate Functions.pptVIP

AGGREGATE FUNCTIONS Prof. Sin-Min Lee Surya Bhagvat CS 157A – Fall 2005 Aggregate Functions What is an aggregate function? An aggregate function summarizes the results of an expression over a number of rows, returning a single value. The general syntax for most of the aggregate functions is as follows: aggregate_function ([DISTINCT|ALL] expression) Commonly used Aggregate functions Examples Employee Table (Contd….) Select on Employee Table Performing SUM SUM (Continued) SUM (Continued…..) SUM (Continued….) AVG Function AVG (Continued….) AVG (Continued….) AVG (Continued…..) AVG (Continued….) AVG (Continued….) AVG (Continued….) AVG (Continued….) AVG (Final SQL) Using MIN AND MAX * * Some of the commonly used aggregate functions are : SUM COUNT AVG MIN MAX Consider the following Employee table: EMPLOYEE ( EMP_ID, NAME, DEPT_NAME, SALARY) CREATE TABLE EMPLOYEE ( EMP_ID NUMBER, NAME VARCHAR2(50), DEPT_NAME VARCHAR2(50), SALARY NUMBER ); Run the following script to insert the records in the table INSERT INTO EMPLOYEE VALUES (100,ABC,ENG,50000); INSERT INTO EMPLOYEE VALUES (101,DEF,ENG,60000); INSERT INTO EMPLOYEE VALUES (102,GHI,PS,50000); INSERT INTO EMPLOYEE VALUES (103,JKL,PS,70000); INSERT INTO EMPLOYEE VALUES (104,MNO,SALES,75000); INSERT INTO EMPLOYEE VALUES (105,PQR,MKTG,70000); INSERT INTO EMPLOYEE VALUES (106,‘STU,SALES,null); COMMIT; After the insert when we query the Employee table we get the following results: Select * from Employee; Query 1: To find the sum of all salaries in the organization: SELECT SUM(SALARY) FROM EMPLOYEE; 375000 Query 2: To find the sum of the salaries grouped by dept SELECT SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_NAME If we take a look at the previous query the information won’t tell us what’s the sum for a particular department. So to include that information we add DEPT_NAME in the SELECT SELECT DEPT_NAME,SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_NAME;