• What are Group Functions?
• Types of Group Functions
• Using Group Functions
• Using AVG and SUM function
• Using MIN and MAX function
• Using COUNT function
• Group functions and NULL values
• Using the NVL Function with Group Function
• Creating GROUP of DATA
• Creating GROUP of DATA: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
• Excluding Group Results
• Excluding Group Results: HAVING Clause
• Using the HAVING clause
• Nesting GROUP Function
• Using a Subquery
• Guideline for using subqueries
• Types of Subqueries
• Single-row subqueries
• Executing Single Row Subqueries
• Using Group Functions in a Subquery
• HAVING clause with subqueries
• Multiple Row Subqueries
• Using ANY operator in Multiple-row subqueries
• Using ALL operator in Multiple-row subqueries
"Group Functions" also known as "Multiple-Row Functions". They operates on set of rows to give one result per group. These set may be the whole table or the table split into groups.
(These are similar to the "aggregate functions" or "Group By" functions in Access)
GROUP BY clause
To identify groups of records to be processed
To restrict the groups displayed
The data types for arguments may be CHAR, VARCHAR2, NUMBER, or DATE.
The Oracle Server implicitly sorts the result set in ascending order when using a GROUP BY clause. To override the default ordering, DESC can be used in an ORDER by clause. Some simple examples:
SELECT COUNT(DISTINCT Employee_city)
SELECT * | column1, column2, …
GROUP BY column1, column2, …
HAVING group condition
**The ALL keyword is the default (includes all values except Nulls)
**The DISTINCT keyword returns only unique values
**Group functions ignore NULL values
Exception: COUNT(*) which means "Count all rows"
Using SUM and AVG function
Calculates the total amount in a numeric field for a group of records.
SUM(n)where n is a numeric column
SUM(ALL n)the same as above
SUM(DISTINCT n)returns only the unique numeric values
SELECT SUM(Salary) "Total Salary"
SELECT SUM(Salary) "Total Salary of E_CITY_001"
Syntax: AVG(column containing numeric data)
AVG(DISTINCT [column containing numeric data] )
DISTINCT keyword returns only unique values
What's the average profit generated?...