Oracle SQL: Reporting Aggregated Data Using the Group Functions

Types of Group Functions

1. AVG 
2. COUNT 
3. MAX
4. MIN 
5. STDDEV 
6. SUM
7. VARIANCE

Guidelines for Using Group Functions

• DISTINCT makes the function consider only nonduplicate values; ALL makes it consider every value, including duplicates. The default is ALL and therefore does not need to be specified.
• The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE. 
• All group functions ignore null values. To substitute a value for null values, use the NVL, NVL2, or COALESCE functions.

Group Functions: Syntax
SELECT     [column,] group_function(column), ...
FROM  table
[WHERE  condition]
[GROUP BY  column]
[ORDER BY  column];

Using the Group Functions
You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.

                                             <<Back                 Next>>