5 Aggregating Data Using Group Functions - cchristopherlee.pptVIP

5 Aggregating Data Using Group Functions Important Legal Notice: Materials on this lecture are from a book titled “Oracle Education” by Kochhar, Gravina, and Nathan (1999), published by Oracle Corp. For further information, visit This presentation must be used for only education purpose for students at Central Washington University which is a member of Oracle Academic Initiatives (OAI) and has used Oracle systems for HRIS Accounting Systems as a database platform embedded on its PeopleSoft ERP system, since 1999. Objectives After completing this lesson, you should 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 What are Group Functions? Types of Group Functions AVG COUNT MAX MIN STDDEV SUM VARIANCE Using Group Functions Using AVG and SUM Functions Using MIN and MAX Functions Using the COUNT Function Using the COUNT Function Group Functions and Null Values Using the NVL Function with Group Functions Creating Groups of Data Creating Groups of Data:GROUP BY Clause Using the GROUP BY Clause Using the GROUP BY Clause Grouping by More Than One Column Using the GROUP BY Clause on Multiple Columns Illegal Queries Using Group Functions Illegal QueriesUsing Group Functions Excluding Group Results Excluding Group Results: HAVING Clause Using the HAVING Clause Using the HAVING Clause Nesting Group Functions Summary Practice Overview Showing different queries that use group functions Grouping by rows to achieve more than one result Excluding groups by using the HAVING clause Group Functions (continued) You can use MAX and MIN functions for any datatype. The slide example displays the most junior and the most senior employee. The following example displays the employee name that is first and the employee name that is last in an alphabetized list of all employees. SQL SELECT MIN(ename), MAX(ename) 2 FROM emp