Oracle SQL Tutorials – Chapter 4

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;

 

COUNT

  • Let’s list the count of the employees and how many different positions.
SELECT COUNT (*), COUNT (DISTINCT job_id)

FROM hr.employees;

 

DISTINCT

  • 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;

 

HAVING STATEMENT

  • 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);

Leave a Reply

Your email address will not be published. Required fields are marked *