Chapter 4 – Group Functions
- AVG Function
- SUM Function
- MIN Function
- MAX Function
- COUNT Function
- DISTINCT Function
- GROUP BY Statement
- HAVING Statement
- One row functions can be applied to a one row of a table and returns a single row.
- But group functions applied to a whole table. Also can be applied to sub-groups, using GROUP BY statement.
AVG & SUM
- Let’s list the average and the sumof the salaries.
SELECT AVG (salary), SUM (salary) FROM hr.employees;
MIN & MAX
- Let’s select the minimum and the maximum salary.
SELECT MIN (salary), MAX (salary) FROM hr.employees;
- Let’s list the count of the employees and how many different positions.
SELECT COUNT (*), COUNT (DISTINCT job_id) FROM hr.employees;
- The Distinct command, groups the data and shows the results just one time.
- Let’s list all the positions.
SELECT DISTINCT job_id FROM hr.employees;
GROUP BY STATEMENT
- With this statement, the rows in the table can be divided into smaller groups.
- The columns in the Group By statement, must be added to Select statement.
- The aliases can’t be used in Group By statement.
- Let’s list the average salaries of departments.
SELECT department_id, AVG (salary) FROM hr.employees GROUP BY department_id ORDER BY 1;
- Let’s write another querry using Group By function.
SELECT department_id, job_id, AVG (salary), SUM (salary), COUNT (*) Employees FROM hr.employees GROUP BY department_id, job_id ORDER BY department_id;
- Where statement can’t be used with group functions.
- So, the Having statement is used instead of where condition.
- Let’s list the positions which average salaries are higher than 10,000.
SELECT job_id, AVG (salary) FROM hr.employees GROUP BY job_id HAVING AVG (salary) > 10000;
- Here is another example of usage having statement.
SELECT job_id, SUM (salary) FROM hr.employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM (salary) > 13000 ORDER BY SUM (salary);
NESTED GROUP FUNCTIONS
- Here are two examples and outputs of nested functions.
SELECT MAX (AVG (salary)) FROM hr.employees GROUP BY department_id;
SELECT department_id, AVG (salary) FROM hr.employees GROUP BY department_id HAVING (MAX (salary)) > ALL ( SELECT AVG (salary) FROM hr.employees GROUP BY department_id);