Lesson 05

Reporting Aggregated Data Using the Group Functions

?# What are group functions?

Group functions operate on sets of rows to give one result per group.

!# Types of Group Functions

* AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE

!# Group Functions: Syntax

Select group_function(column), ...
From table
[Where condition]
[Order By column];

You can use AVG and SUM for numeric data.

Select AVG(salary), MAX(salary), MIN(salary), SUM(salary)
From employees
Where job_id LIKE '%REP%';

You can use MIND and MAX for numeric, character, and date data types

Select MIN(hire_date), MAX(hire_date)
From employees;

COUNT(*) returns the number of rows in a tabel

Select COUNT(*)
From employees
Where department_id = 50;

COUNT(DISTINCT expr) return the number of distinct non-null values of exp.
To display the number of distinct department values in the EMPLOYEES tabel:

Select COUNT(DISTINCT department_id)
From employees;

Group functions ignore null values in the column:

Select AVG(commission_pct)
From employees;

The NVL functions forces group functions to include null values:

Select AVG(NVL(commission_pct, 0 ))
From employees;

Creating Groups of Data:
GROUP BY Clause Syntax

Select column, group_function(column)
From table
[Where condition]
[GROUP BY group_by_expression]
[Order By column];

You can divide rows in a table into smaller groups by using the GROUP BY clause.

All columns in the Select list that are not in group functions must be in the GROUP BY clause.

Select department_id, AVG(salary)
From employees
GROUP BY department_id;

The GROUP BY column does not have to be in the Select list.

Select AVG(salary)
From employees
GROUP BY department_id;

Using the Group by Clause on Multiple Columns

Select department_id, job_id, SUM(salary)
From employees
Where department_id > 40
Group By department_id, job_id
Order By department_id;