Aggregate Fun

Group/Aggregate Functions
Objectives
– Identify the available group functions
– Describe the use of group functions
– Group data by using the GROUP BY clause
– Include or exclude grouped rows by using the HAVING
clause
What Are Group Functions?
Group functions operate on sets of rows to give one result per
EMPLOYEES
Maximum salary in
EMPLOYEES table
…
Types of Group Functions
– AVG
– COUNT
– MAX
– MIN
Group
– STDDEV functions
– SUM
– VARIANCE
Group function has many inputs and

one output.
Group Functions: Syntax
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
Aggregate Functions
Using the AVG and SUM Functions
You can use AVG and SUM for numeric data.
Using the MIN and MAX Functions
You can use MIN and MAX for numeric, character, and date data types.
SELECT MIN(hire_date), MAX(hire_date)

FROM employees;
Using the COUNT Function
COUNT(*) returns the number of rows in a table:
SELECT COUNT(*)
1 FROM employees
WHERE department_id = 50;
COUNT(expr) returns the number of rows with non-

null values
SELECT for the expr:
COUNT(commission_pct)
2 FROM employees
WHERE department_id = 80;
Using the DISTINCT Keyword
– COUNT(DISTINCT expr) returns the number of distinct
non-null values of the expr.
– To display the number of distinct department values in the
EMPLOYEES table:
SELECT COUNT(DISTINCT department_id)
FROM employees;
Group Functions and Null Values
Group functions ignore null values in the column:
SELECT AVG(commission_pct)
1 FROM employees;
The NVL function forces group functions to include null

values:
SELECT AVG(NVL(commission_pct, 0))
2 FROM employees;
Creating Groups of Data
EMPLOYEES
4400
9500
3500 Average
salary in
EMPLOYEES
6400 table for each
department
10033
…
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.
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 ;
Grouping by More Than One Column
EMPLOYEES
Add the
salaries in
the EMPLOYEES
table for
each job,
grouped by
department
…
on Multiple Columns
SELECT department_id dept_id, job_id, SUM(salary)

FROM employees
GROUP BY department_id, job_id ;
Illegal Queries
Using Group Functions
Any column or expression in the SELECT list that is not an aggregate

function must be 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
Column missing in the GROUP BY clause

Illegal Queries
Using Group Functions
– You cannot use the WHERE clause to restrict groups.

– You use the HAVING clause to restrict groups.
– You cannot use group functions in the WHERE 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
Cannot use the WHERE clause to restrict groups
Restricting Group Results
EMPLOYEES
The maximum
salary
per department
when it is
greater than
$10,000
…
Restricting Group Results
with the HAVING Clause
When you use the HAVING clause, the Oracle

server restricts groups as follows:
1.Rows are grouped.
2.The group function is applied.
3.Groups matching the HAVING clause are displayed.
SELECT column, group_function

FROM table
[WHERE condition]
[HAVING group_condition]
[ORDER BY column];
Using the HAVING Clause
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
Using the HAVING Clause
Nesting Group Functions
Display the maximum average salary:
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
Summary of Group Functions
In this lesson, you should have learned how to:
– Use the group functions COUNT, MAX, MIN, and AVG
– Write queries that use the GROUP BY clause
– Write queries that use the HAVING clause
SELECT column, group_function

FROM table
[WHERE condition]
[HAVING group_condition]
[ORDER BY column];
Often applications require grouping rows that have certain
properties and then applying an aggregate function on
one column for each group separately. For this, SQL
provides the clause group by <group column(s)>. This
clause appears after the where clause and must refer to
columns of tables listed in the from clause.
Syntax:-
select <column(s)>
from <table(s)>
where <condition>
group by <group column(s)>
[having <group condition(s)>];
Example: For each department, we want to retrieve the minimum
and maximum salary.
select DEPTNO, min(SAL), max(SAL)
from EMP
group by DEPTNO;
Example: Retrieve the minimum and maximum salary of
clerks for each department having more than three
clerks.
select DEPTNO, min(SAL), max(SAL)
from EMP
where JOB = ’CLERK’
group by DEPTNO
having count() > 3;

Aggregate Fun

Uploaded by

Copyright:

Available Formats

Aggregate Fun

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Aggregate Fun

Uploaded by

Copyright:

Available Formats

Group/Aggregate Functions

Group function has many inputs and

SELECT MIN(hire_date), MAX(hire_date)

COUNT(expr) returns the number of rows with non-

The NVL function forces group functions to include null

SELECT column, group_function(column)

SELECT department_id, AVG(salary)

SELECT department_id dept_id, job_id, SUM(salary)

Any column or expression in the SELECT list that is not an aggregate

SELECT department_id, COUNT(last_name)

SELECT department_id, COUNT(last_name)

Column missing in the GROUP BY clause

– You cannot use the WHERE clause to restrict groups.

WHERE AVG(salary) > 8000

When you use the HAVING clause, the Oracle

SELECT column, group_function

SELECT column, group_function

You might also like