# sql05 - 5 5 Aggregating Data Using Group Functions 5-2...

This preview shows pages 1–9. Sign up to view the full content.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 5 5 Aggregating Data Using Group Functions 5-2 Objectives After completing this lesson, you should After completing this lesson, you should be able to do the following: be able to do the following: • Identify the available group functions • Describe the use of group functions • Group data using the GROUP BY clause • Include or exclude grouped rows by using the HAVING clause 5-3 What Are Group Functions? Group functions operate on sets of rows to give Group functions operate on sets of rows to give one result per group. one result per group. EMP EMP “maximum maximum salary in salary in the EMP table” the EMP table” DEPTNO SAL--------- ---------10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL)---------5000 5-4 Types of Group Functions • AVG • COUNT • MAX • MIN • STDDEV • SUM • VARIANCE 5-5 Using Group Functions SELECT column , group_function(column) FROM table [WHERE condition ] [ORDER BY column ]; 5-6 Using AVG and SUM Functions AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)-------- --------- --------- ---------1400 1600 1250 5600 You can use AVG and SUM for numeric data. You can use AVG and SUM for numeric data. SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES%'; 5-7 Using MIN and MAX Functions You can use MIN and MAX for any datatype. You can use MIN and MAX for any datatype. SQL> SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp ; MIN(HIRED MAX(HIRED--------- ---------17-DEC-80 12-JAN-83 5-8 Using the COUNT Function COUNT(*)---------6 SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30; COUNT(*) returns the number of rows in a COUNT(*) returns the number of rows in a table....
View Full Document

{[ snackBarMessage ]}

### Page1 / 25

sql05 - 5 5 Aggregating Data Using Group Functions 5-2...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online