SQL Group Functions

1. SQL Group Functions Introduction to Oracle9i: SQL

2. 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 Oracle9i: SQL

3. Chapter Objectives • Use the MIN and MAX functions with non-numeric 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 Oracle9i: SQL

4. 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 Oracle9i: SQL

5. 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 Oracle9i: SQL

6. SUM Function Calculates total amount stored in a numeric column for a group of rows Introduction to Oracle9i: SQL

7. AVG Function Calculates average of numeric values in a specified column Introduction to Oracle9i: SQL

8. COUNT Function Two purposes: • Count non-NULL values • Count total records, including those with NULL values Introduction to Oracle9i: SQL

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

10. COUNT Function – NULL Values Include asterisk in argument to count number of rows Introduction to Oracle9i: SQL

11. MAX Function Returns largest value Introduction to Oracle9i: SQL

12. MIN Function Returns smallest value Introduction to Oracle9i: SQL

13. 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 Oracle9i: SQL

14. GROUP BY Example Introduction to Oracle9i: SQL

15. HAVING Clause// Serves as the WHERE clause for grouped data Introduction to Oracle9i: SQL

16. Order of Clause Evaluation When included in the same SELECT statement, evaluated in order of: • WHERE • GROUP BY • HAVING Introduction to Oracle9i: SQL

17. Nesting Functions • Inner function resolved first • Maximum nesting depth: 2 Introduction to Oracle9i: SQL

18. Statistical Group Functions • Based on normal distribution • Includes: • STDDEV • VARIANCE Introduction to Oracle9i: SQL

19. STDDEV Function Calculates standard deviation for grouped data Introduction to Oracle9i: SQL

20. VARIANCE Function Determines data dispersion within a group Introduction to Oracle9i: SQL