Oracle Lab 7

Aggregating Data Using Group
Functions
• AVG( [DISTINCT | ALL] n)

• COUNT
• MAX
• MIN
• SUM
Other clauses we will learn
GROUP BY
HAVING
1
What Are Group Functions ?
DEPTNO SAL
--------------------- ---------------
10 2450
10 5000
10 1300
20 800
20 1100 “Maximum salary MAX(SAL)
20 3000 in the EMP table”
---------------
20 3000
5000
20 2975
30 1600
30 2850
30 1250
30 950
30 1250
30 1250
2
Using Group Functions
SELECT [COLUMN,] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column
[ORDER BY column];
• DISTINCT makes the function consider only non-duplicate

values; ALL makes it consider every value including
duplicates. The default is ALL and therefore does not need to
be specified.
• The data type for the arguments may be CHAR, VARCHAR2 ,
NUMBER, or DATE where expr is listed.
3
Using MIN and MAX Functions
You can use MIN and MAX for any data-type
SELECT MIN(hiredate), MAX(hiredate)

FROM emp;
MIN(HIRED MAX(HIRED
---------------- -----------------
17-DEC-80 12-JAN-83
4
Using AVG and SUM Functions
SELECT AVG(sal), MAX(sal),MIN(sal), SUM(sal)

FROM emp
WHERE job LIKE ‘SALES%’;
5
Using the COUNT Function
COUNT(*) returns the number of rows in a table.
SELECT COUNT(*)
FROM emp
WHERE deptno = 30;
6
Group Functions and Null Values
Group functions ignore null values in the column.
SELECT AVG(comm)
FROM emp;
7
Creating Groups of Data
DEPTNO SAL
--------------------- ---------------
10 2450
10 5000 2916.6667
10 1300
20 800
20 1100 average
MAX(SAL)
salary in
20 3000 2175 EMP table ---------------
20 3000 for each 5000
20 2975 department
30 1600
30 2850
30 1250 1566.6667
30 950
30 1250
30 1250 8
Creating Groups of Data :
GROUP BY Clause
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group-by-expression]
[ORDER BY column];
Divide rows in a table into smaller groups by using the

GROUP BY clause.
9
Guidelines
• If you include a group function in a SELECT clause, you
cannot select individual results as well unless the individual
column appears in the GROUP BY clause. You will receive
an error message if you fail to include the column list.
• Using a WHERE clause, you can pre-exclude rows before

dividing them into groups.
• You must include the columns in GROUP BY clause.
• You cannot use the column alias in the GROUP BY clause.
10
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 deptno, AVG(sal)

FROM emp
GROUP BY deptno;
11
Using the GROUP BY Clause
GROUP BY column does not have to be the SELECT list.
SELECT AVG(sal)
FROM emp
GROUP BY deptno;
Grouping by More Than One Column
DEPTNO JOB SAL
------------ ---------------- ---------------
10 MANAGER 2450
10 PRESIDENT 5000
10 CLERK 1300 DEPTNO JOB SUM(SAL)
20 CLERK 800 ------------ ---------------- ---------------
20 CLERK 1100 10 CLERK 1300
20 ANALYST 3000 10 MANAGER 2450
20 ANALYST 3000 10 PRESIDENT 5000
20 MANAGER 2975 20 ANALYST 6000
30 SALESMAN 1600 .....
30 MANAGER 2850 9 rows selected.
30 SALESMAN 1250
30 CLERK 950
30 SALESMAN 1250
30 SALESMAN 1250
“sum salaries in the EMP table for

each job, grouped by department.”
13
Using the GROUP BY Clause on
Multiple Columns
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno, job;
DEPTNO JOB SUM(SAL)

-------------- ------------- ------------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
.....
9 rows selected.
14
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 deptno, count(ename)

FROM emp;
SELECT deptno, COUNT(ename)

*
ERROR at line 1:
ORA – 00937 : not a single-group function
15
Illegal Queries Using Group Functions
• You cannot use the WHERE clause to restrict groups.
• You use the HAVING clause to restrict groups.
SELECT deptno, avg(sal)

FROM emp
WHERE AVG(sal) > 2000
GROUP BY deptno;
WHERE AVG(sal) > 2000

*
ERROR at line 3:
ORA – 00934 : group function is not allowed
here
16
Excluding Group Results
DEPTNO SAL
-------------- --------------
10 2450
10 5000 5000
10 1300
20 800 “Maximum
20 1100 DEPTNO MAX(SAL)
20 3000 3000 salary per ------------ ----------------
20 3000 department 10 5000
20 2975 greater than 20 3000
30 1600 2900”
30 2850
30 1250 2850
30 950
30 1500
30 1250
17
Excluding Group Results :
HAVING Clause
Use the HAVING clause to restrict groups
• Rows are grouped.
• The group function is applied.
• Groups matching the HAVING clause are displayed.
SELECT column, group_function(column)

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
18
Using the HAVING Clause
SELECT deptno, max(sal)

FROM emp
GROUP BY deptno
HAVING max(sal) > 2000;
DEPTNO MAX(SAL)
------------------- -------------------
10 5000
20 3000
19
Using the HAVING Clause
SELECT job, SUM(sal) PAYROLL

FROM emp
WHERE job NOT LIKE ‘SALES%’
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal)
JOB MAX(SAL)
-------------- -------------------
ANALYST 6000
MANAGER 8275
20

Oracle Lab 7

Uploaded by

Copyright:

Available Formats

Oracle Lab 7

Uploaded by

Document Information

Original Description:

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

Oracle Lab 7

Uploaded by

Copyright:

Available Formats

Aggregating Data Using Group

• AVG( [DISTINCT | ALL] n)

Other clauses we will learn

• DISTINCT makes the function consider only non-duplicate

You can use MIN and MAX for any data-type

SELECT MIN(hiredate), MAX(hiredate)

SELECT AVG(sal), MAX(sal),MIN(sal), SUM(sal)

COUNT(*) returns the number of rows in a table.

Group functions ignore null values in the column.

Divide rows in a table into smaller groups by using the

• Using a WHERE clause, you can pre-exclude rows before

• You must include the columns in GROUP BY clause.

• You cannot use the column alias in the GROUP BY clause.

All columns in the SELECT list that are not in group

SELECT deptno, AVG(sal)

GROUP BY column does not have to be the SELECT list.

“sum salaries in the EMP table for

DEPTNO JOB SUM(SAL)

SELECT deptno, count(ename)

SELECT deptno, COUNT(ename)

SELECT deptno, avg(sal)

WHERE AVG(sal) > 2000

SELECT column, group_function(column)

SELECT deptno, max(sal)

SELECT job, SUM(sal) PAYROLL

You might also like