2 Day Developer > Using SQL > Using Pseudocolumns, Sequen... > Using Aggregate Functions
Using Aggregate Functions |
Previous |
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';