Using Aggregate Functions

Previous
Previous
Next
Next

Aggregate or group functions operate on sets of rows to give one result for each group. These sets can be the entire table or the table split into groups.

Example: Using SQL Aggregate Functions shows how to use aggregate functions on collections of data in the database. Aggregate functions include AVG, COUNT, DENSE_RANK, MAX, MIN, PERCENT_RANK, RANK, STDDEV, and SUM. The GROUP BY clause is used to select groups of rows by a specified expression, and returns one row of summary information for each group. The HAVING clause is used to specify which groups to include, or exclude, from the output based on a group condition. The DISTINCT clause causes an aggregate function to consider only distinct values of the argument expression. The ALL clause, which is the default behavior, causes an aggregate function to consider duplicate values.

Using SQL Aggregate Functions

-- you can use COUNT to count the employees with manager 122
-- note the use of a column alias Employee Count
SELECT COUNT(*) "Employee Count" FROM employees WHERE manager_id = 122;

-- count the employees grouped by manager, also sort the groups
SELECT COUNT(*) "Employee Count", manager_id  FROM employees 
  GROUP BY manager_id 
  ORDER BY manager_id;

-- count the number of employees that receive a commission 
-- this returns the count where the commission_pct is not NULL
SELECT COUNT(commission_pct) FROM employees;

-- count the number of distinct department IDs assigned to the employees
-- this returns a number that does not include duplicates
SELECT COUNT(DISTINCT department_id) FROM employees;

-- you can use MIN, MAX, and AVG to find the minimum, maximum, and average
-- salaries for employees with manager 122
SELECT MIN(salary), MAX(salary), AVG(salary) FROM employees 
  WHERE manager_id = 122;

-- this computes the minimum, maximum, and average salary by job ID groups
-- the job ID groups are sorted in alphabetical order
SELECT MIN(salary), MAX(salary), AVG(salary), job_id FROM employees 
  GROUP BY job_id 
  ORDER BY job_id;

-- the following returns the minimum and maximum salaries for employees grouped
-- by department for those groups having a minimum salary less than $7,000
SELECT department_id, MIN(salary), MAX (salary) FROM employees
   GROUP BY department_id 
   HAVING MIN(salary) < 7000
   ORDER BY MIN(salary);

-- the following uses the PERCENT_RANK function to return the percent ranking
-- for a $11,000 salary among the employees who are managers
-- in this example, a percent ranking of 0 corresponds to the highest salary
SELECT PERCENT_RANK(11000) WITHIN GROUP
   (ORDER BY salary DESC) "Rank of $11,000 among managers" 
   FROM employees WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR';

-- the following uses the RANK function to return the ranking for a $2,600 salary 
-- among the employees who are clerks, 
-- in this example a ranking of 1 corresponds to the highest salary in the group
SELECT RANK(2600) WITHIN GROUP
   (ORDER BY salary DESC) "Rank of $2,600 among clerks"
   FROM employees WHERE job_id LIKE '%CLERK';

-- the following uses RANK to show the ranking of SH_CLERK employees by salary
-- identical salary values receive the same rank and cause nonconsecutive ranks
SELECT job_id, employee_id, last_name, salary, RANK() OVER
   (PARTITION BY job_id ORDER BY salary DESC) "Salary Rank"
   FROM employees WHERE job_id = 'SH_CLERK';

-- the following uses DENSE_RANK to show the ranking of SH_CLERK employees 
-- by salary, identical salary values receive the same rank and 
-- rank numbers are consecutive (no gaps in the ranking)
SELECT job_id, employee_id, last_name, salary, DENSE_RANK() OVER
   (PARTITION BY job_id ORDER BY salary DESC) "Salary Rank (Dense)"
   FROM employees WHERE job_id = 'SH_CLERK';

-- the following computes the cumulative standard deviation of the salaries 
-- for ST CLERKs ordered by hire_date
SELECT employee_id, salary, hire_date, STDDEV(salary) 
   OVER (ORDER BY hire_date) "Std Deviation of Salary"
   FROM employees WHERE job_id = 'ST_CLERK';