SQL Group Functions Introduction to Oracle 9 i

Chapter Objectives • Differentiate between single-row and multiple-row functions • Use the SUM and

Chapter Objectives • Use the MIN and MAX functions with nonnumeric fields • Determine

Chapter Objectives • State the maximum depth for nesting group functions • Nest a

Group Functions • Return one result per group of rows processed • Also called

SUM Function Calculates total amount stored in a numeric column for a group of

AVG Function Calculates average of numeric values in a specified column Introduction to Oracle

COUNT Function Two purposes: – Count non-NULL values – Count total records, including those

COUNT Function – Non-NULL Values Include column name in argument to count number of

COUNT Function – NULL Values Include asterisk in argument to count number of rows

MAX Function Returns largest value Introduction to Oracle 9 i: SQL 11

MIN Function Returns smallest value Introduction to Oracle 9 i: SQL 12

GROUP BY Clause • Used to group data • Must be used for individual

GROUP BY Example Introduction to Oracle 9 i: SQL 14

HAVING Clause// Serves as the WHERE clause for grouped data Introduction to Oracle 9

Order of Clause Evaluation When included in the same SELECT statement, evaluated in order

Nesting Functions • Inner function resolved first • Maximum nesting depth: 2 Introduction to

Statistical Group Functions • Based on normal distribution • Includes: – STDDEV – VARIANCE

STDDEV Function Calculates standard deviation for grouped data Introduction to Oracle 9 i: SQL

VARIANCE Function Determines data dispersion within a group Introduction to Oracle 9 i: SQL

Slides: 20

Download presentation

SQL Group Functions Introduction to Oracle 9 i: SQL 1

Chapter Objectives • Differentiate between single-row and multiple-row functions • Use the SUM and AVG functions for numeric calculations • Use the COUNT function to return the number of records containing non-NULL values • Use COUNT(*) to include records containing NULL values Introduction to Oracle 9 i: SQL 2

Chapter Objectives • Use the MIN and MAX functions with nonnumeric fields • Determine when to use the GROUP BY clause to group data • Identify when the HAVING clause should be used • List the order of precedence for evaluating WHERE, GROUP BY, and HAVING clauses Introduction to Oracle 9 i: SQL 3

Chapter Objectives • State the maximum depth for nesting group functions • Nest a group function inside a single-row function • Calculate the standard deviation and variance of a set of data, using the STDDEV and VARIANCE functions Introduction to Oracle 9 i: SQL 4

Group Functions • Return one result per group of rows processed • Also called multiple-row and aggregate functions • All group functions ignore NULL values except COUNT(*) • Use DISTINCT to suppress duplicate values Introduction to Oracle 9 i: SQL 5

SUM Function Calculates total amount stored in a numeric column for a group of rows Introduction to Oracle 9 i: SQL 6

AVG Function Calculates average of numeric values in a specified column Introduction to Oracle 9 i: SQL 7

COUNT Function Two purposes: – Count non-NULL values – Count total records, including those with NULL values Introduction to Oracle 9 i: SQL 8

COUNT Function – Non-NULL Values Include column name in argument to count number of occurrences Introduction to Oracle 9 i: SQL 9

COUNT Function – NULL Values Include asterisk in argument to count number of rows Introduction to Oracle 9 i: SQL 10

MAX Function Returns largest value Introduction to Oracle 9 i: SQL 11

MIN Function Returns smallest value Introduction to Oracle 9 i: SQL 12

GROUP BY Clause • Used to group data • Must be used for individual column in the SELECT clause with a group function • Cannot reference column alias Introduction to Oracle 9 i: SQL 13

GROUP BY Example Introduction to Oracle 9 i: SQL 14

HAVING Clause// Serves as the WHERE clause for grouped data Introduction to Oracle 9 i: SQL 15

Order of Clause Evaluation When included in the same SELECT statement, evaluated in order of: – WHERE – GROUP BY – HAVING Introduction to Oracle 9 i: SQL 16

Nesting Functions • Inner function resolved first • Maximum nesting depth: 2 Introduction to Oracle 9 i: SQL 17

Statistical Group Functions • Based on normal distribution • Includes: – STDDEV – VARIANCE Introduction to Oracle 9 i: SQL 18

STDDEV Function Calculates standard deviation for grouped data Introduction to Oracle 9 i: SQL 19

VARIANCE Function Determines data dispersion within a group Introduction to Oracle 9 i: SQL 20