Aggregate Functions
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in
ORDER
BY
and HAVING
clauses. They are commonly used with the GROUP
BY
clause in a SELECT
statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP
BY
clause, the elements of the select list can be aggregate functions, GROUP
BY
expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the
GROUP
BY
clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING
clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
Using the GROUP BY Clause: Examples To return the minimum and maximum salaries for each department in the
employees
table, issue the following statement:SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id ORDER BY department_id;
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary) FROM employees WHERE job_id = 'PU_CLERK' GROUP BY department_id ORDER BY department_id;
Using the GROUP BY CUBE Clause: Example To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables
hr.employees
and hr.departments
:SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY CUBE (department_name, job_id) ORDER BY department_name, job_id; DEPARTMENT_NAME JOB_ID Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 . . . All Departments ST_MAN 5 87360 All Departments All Jobs 107 77798.1308
Using the GROUPING SETS Clause: Example The following example finds the sum of sales aggregated for three precisely specified groups:
(channel_desc, calendar_month_desc, country_id)
(channel_desc, country_id)
(calendar_month_desc, country_id)
Without the
GROUPING
SETS
syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and UNION
them, or run a query with a CUBE(channel_desc, calendar_month_desc, country_id)
operation and filter out five of the eight groups it would generate.SELECT channel_desc, calendar_month_desc, co.country_id, TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$ FROM sales, customers, times, channels, countries co WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND customers.country_id = co.country_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND co.country_iso_code IN ('UK', 'US') GROUP BY GROUPING SETS( (channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), (calendar_month_desc, co.country_id) ); CHANNEL_DESC CALENDAR CO SALES$ -------------------- -------- -- -------------- Direct Sales 2000-09 UK 1,378,126 Direct Sales 2000-10 UK 1,388,051 Direct Sales 2000-09 US 2,835,557 Direct Sales 2000-10 US 2,908,706 Internet 2000-09 UK 911,739 Internet 2000-10 UK 876,571 Internet 2000-09 US 1,732,240 Internet 2000-10 US 1,893,753 Direct Sales UK 2,766,177 Direct Sales US 5,744,263 Internet UK 1,788,310 Internet US 3,625,993 2000-09 UK 2,289,865 2000-09 US 4,567,797 2000-10 UK 2,264,622 2000-10 US 4,802,459
Using the HAVING Condition: Example To return the minimum and maximum salaries for the employees in each department whose lowest salary is less than $5,000, issue the next statement:SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000 ORDER BY department_id; DEPARTMENT_ID MIN(SALARY) MAX(SALARY) ------------- ----------- ----------- 10 4400 4400 30 2500 11000 50 2100 8200 60 4200 9000The following example uses a correlated subquery in aHAVING
clause that eliminates from the result set any departments without managers and managers without departments:SELECT department_id, manager_id FROM employees GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN (SELECT department_id, manager_id FROM employees x WHERE x.department_id = employees.department_id) ORDER BY department_id;Using the ORDER BY Clause: Examples To select all purchasing clerk records fromemployees
and order the results by commission in descending order, issue the following statement:SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY commission_pct DESC;To select information fromemployees
ordered first by ascending department number and then by descending salary, issue the following statement:SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC;To select the same information as the previousSELECT
and use the positionalORDER
BY
notation, issue the following statement, which orders by ascendingdepartment_id
, then descendingsalary
, and finally alphabetically bylast_name
:SELECT last_name, department_id, salary FROM employees ORDER BY 2 ASC, 3 DESC, 1;The aggregate functions are:
Aggregate ExampleThe following example calculates the average salary of all employees in thehr.employees
table:SELECT AVG(salary) "Average" FROM employees; Average -------- 6425The following example calculates, for each employee in theemployees
table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees; MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG ---------- ------------------------- --------- ---------- ---------- 100 Kochhar 21-SEP-89 17000 17000 100 De Haan 13-JAN-93 17000 15000 100 Raphaely 07-DEC-94 11000 11966.6667 100 Kaufling 01-MAY-95 7900 10633.3333 100 Hartstein 17-FEB-96 13000 9633.33333 100 Weiss 18-JUL-96 8000 11666.6667 100 Russell 01-OCT-96 14000 11833.3333Aggregate ExamplesThe following examples useCOUNT
as an aggregate function:SELECT COUNT(*) "Total" FROM employees; Total ---------- 107 SELECT COUNT(*) "Allstars" FROM employees WHERE commission_pct > 0; Allstars --------- 35 SELECT COUNT(commission_pct) "Count" FROM employees; Count ---------- 35 SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees; Managers ---------- 18The following example calculates, for each employee in theemployees
table, the moving count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary.SELECT last_name, salary, COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count FROM employees; LAST_NAME SALARY MOV_COUNT ------------------------- ---------- ---------- Olson 2100 3 Markle 2200 2 Philtanker 2200 2 Landry 2400 8 Gee 2400 8 Colmenares 2500 10 Patel 2500 10Aggregate ExampleThe following example determines the highest salary in thehr.employees
table:SELECT MAX(salary) "Maximum" FROM employees; Maximum ---------- 24000The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees; MANAGER_ID LAST_NAME SALARY MGR_MAX ---------- ------------------------- ---------- ---------- 100 Kochhar 17000 17000 100 De Haan 17000 17000 100 Raphaely 11000 17000 100 Kaufling 7900 17000 100 Fripp 8200 17000 100 Weiss 8000 17000Aggregate ExampleThe following statement returns the earliest hire date in thehr.employees
table:SELECT MIN(hire_date) "Earliest" FROM employees; Earliest --------- 17-JUN-87The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.SELECT manager_id, last_name, hire_date, salary, MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date RANGE UNBOUNDED PRECEDING) AS p_cmin FROM employees; MANAGER_ID LAST_NAME HIRE_DATE SALARY P_CMIN ---------- ------------------------- --------- ---------- ---------- 100 Kochhar 21-SEP-89 17000 17000 100 De Haan 13-JAN-93 17000 17000 100 Raphaely 07-DEC-94 11000 11000 100 Kaufling 01-MAY-95 7900 7900 100 Hartstein 17-FEB-96 13000 7900 100 Weiss 18-JUL-96 8000 7900 100 Russell 01-OCT-96 14000 7900 100 Partners 05-JAN-97 13500 7900 100 Errazuriz 10-MAR-97 12000 7900Aggregate ExampleThe following example calculates the sum of all salaries in the samplehr.employees
table:SELECT SUM(salary) "Total" FROM employees; Total ---------- 691400The following example calculates, for each manager in the sample tablehr.employees
, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary. You can see that Raphaely and Cambrault have the same cumulative total. This is because Raphaely and Cambrault have the identical salaries, so Oracle Database adds together their salary values and applies the same cumulative total to both rows.SELECT manager_id, last_name, salary, SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM employees; MANAGER_ID LAST_NAME SALARY L_CSUM ---------- --------------- ---------- ---------- 100 Mourgos 5800 5800 100 Vollman 6500 12300 100 Kaufling 7900 20200 100 Weiss 8000 28200 100 Fripp 8200 36400 100 Zlotkey 10500 46900 100 Raphaely 11000 68900 100 Cambrault 11000 68900 100 Errazuriz 12000 80900 . . . 149 Taylor 8600 30200 149 Hutton 8800 39000 149 Abel 11000 50000 201 Fay 6000 6000 205 Gietz 8300 8300 King 24000 24000