What is a grouping function
The grouping function acts on a group of data and returns a value to a group of data.
Group function type
AVG
COUNT
MAX
MIN
STDDEV
SUM
Group function syntax
SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
AVG and SUM functions
AVG and SUM functions can be used for numeric data.
SQL> SELECT AVG(salary), MAX(salary), 2 MIN(salary), SUM(salary) 3 FROM employees 4 WHERE job_id LIKE '%REP%'; AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) ----------- ----------- ----------- ----------- 8272.727272 11500 6000 273000
MIN and MAX functions
You can use the MIN and MAX functions for any data type.
SQL> SELECT MIN(hire_date), MAX(hire_date) 2 FROM employees; MIN(HIRE_DATE) MAX(HIRE_DATE) -------------- -------------- 1987/6/17 2000/4/21
COUNT function
COUNT(*) returns the total number of records in the table, applicable to any data type.
SQL> SELECT COUNT(*) 2 FROM employees 3 WHERE department_id = 50; COUNT(*) ---------- 45
COUNT function
COUNT(expr) returns the total number of records whose expr is not empty.
SQL> SELECT COUNT(commission_pct) 2 FROM employees 3 WHERE department_id = 50; COUNT(COMMISSION_PCT) --------------------- 0
Group functions and nulls
Group functions ignore null values.
SQL> SELECT AVG(commission_pct) 2 FROM employees; AVG(COMMISSION_PCT) ------------------- 0.222857142857143 SQL> Select avg(commission_pct),sum(commission_pct)/107, 2 sum(commission_pct)/count(commission_pct) 3 From employees; AVG(COMMISSION_PCT) SUM(COMMISSION_PCT)/107 SUM(COMMISSION_PCT)/COUNT(COMMISSION_PCT) ------------------- ----------------------- ----------------------------------------- 0.222857142857143 0.0728971962616822 0.222857142857143
Using NVL functions in group functions
The NVL function prevents grouping functions from ignoring null values.
SQL> SELECT AVG(NVL(commission_pct, 0)) 2 FROM employees; AVG(NVL(COMMISSION_PCT,0)) -------------------------- 0.0728971962616822
DISTINCT keyword
COUNT(DISTINCT expr) returns the total number of non empty and non duplicate records
SQL> SELECT COUNT(DISTINCT department_id) 2 FROM employees; COUNT(DISTINCTDEPARTMENT_ID) ---------------------------- 11
Grouping data
Grouping data: GROUP BY clause syntax
You can use the GROUP BY clause to divide the data in a table into groups
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
Clear: WHERE must be placed after FROM
GROUP BY clause
In the SELECT list, all columns that are not included in the group function should be included
In the GROUP BY clause.
SQL> SELECT department_id, AVG(salary) 2 FROM employees 3 GROUP BY department_id ; DEPARTMENT_ID AVG(SALARY) ------------- ----------- 7000 100 8600 30 4150 90 19333.33333 20 9500 70 10000 110 10150 50 3475.555555 80 8955.882352 40 6500 60 5760 10 4400
GROUP BY clause
SQL> SELECT AVG(salary) 2 FROM employees 3 GROUP BY department_id ; AVG(SALARY) ----------- 7000 8600 4150 19333.33333 9500 10000 10150 3475.555555 8955.882352 6500 5760 4400 12 rows selected
Group with multiple columns
Contains more than one column in the GROUP BY clause
SQL> SELECT department_id dept_id, job_id, SUM(salary) 2 FROM employees 3 GROUP BY department_id, job_id ; DEPT_ID JOB_ID SUM(SALARY) ------- ---------- ----------- 110 AC_ACCOUNT 8300 90 AD_VP 34000 50 ST_CLERK 55700 80 SA_REP 243500 50 ST_MAN 36400 80 SA_MAN 61000 110 AC_MGR 12000 90 AD_PRES 24000 60 IT_PROG 28800 20 MK_MAN 13000 100 FI_MGR 12000 30 PU_CLERK 13900 50 SH_CLERK 64300 100 FI_ACCOUNT 39600 SA_REP 7000 70 PR_REP 10000 10 AD_ASST 4400 20 MK_REP 6000 30 PU_MAN 11000 40 HR_REP 6500 20 rows selected
Illegal use of group functions
All columns contained in the SELECT list but not in the group function are
Must be included in the GROUP BY clause.
SELECT department_id, COUNT(last_name) FROM employees;
SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function
Missing column in GROUP BY clause
Group functions cannot be used in the WHERE clause.
You can use group functions in the HAVING clause.
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;
WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here
Group functions cannot be used in the WHERE clause
Filter grouping
Filtering groups: HAVING clause
Use HAVING to filter groups:
- Rows have been grouped.
- Group functions are used.
- Groups that meet the conditions in the HAVING clause are displayed.
SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
HAVING clause
SQL> SELECT department_id, MAX(salary) 2 FROM employees 3 GROUP BY department_id 4 HAVING MAX(salary)>10000 ; DEPARTMENT_ID MAX(SALARY) ------------- ----------- 100 12000 30 11000 90 24000 20 13000 110 12000 80 14000 6 rows selected
Nested group function
Displays the maximum value of the average salary of each department
SQL> SELECT MAX(AVG(salary)) 2 FROM employees 3 GROUP BY department_id; MAX(AVG(SALARY)) ---------------- 19333.3333333333
Summary
Use group functions: avg(),sum(),max(),min(),count()
Use the GROUP BY clause in the query.
Use the HAVING clause in the query.
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];