Oracle 1Z0-007: Aggregating Data using Group Functions

Aggregating Data using Group Functions

Identify the available group functions

AVG - average of value for a group of rows
select ave(pay) from payroll;
COUNT - count rows of columns
FIRST - returns the row ranked first using DENSE_RANK

SELECT department_id, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) WORST, MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) BEST FROM employees GROUP BY department_id;
LAST - returns the row ranked last using DENSE_RANK
MAX - Maximum of all value for groups of rows
MIN - Minimum of all values for groups of rows
STDDEV - Sample standard deviation of an expression STDDEV(<expression>)

SELECT STDDEV(salary) DEVIATION FROM employees;
SUM - Sum of all values for groups of rows
VARIANCE - variance of an expression
VARIANCE(>value<)
select variance(initial_extent) from users;

Use group functions

Group functions return a single result based on many rows, as opposed to single-row functions.e.g The group COUNT function returns the number of rows returned. These functions are valid in the select list of a query and the GROUP BY clause ONLY. Most functions can accept qualifiers as their arguments . These qualifiers are DISTINCT and ALL . If the DISTINCT qualifier is passed, then only distinct values returned by the query are considered. The ALL qualifier causes the function to consider all of the values returned by the query. If none is specified then ALL is the default.

Group data using the GROUP BY clause

select last_name, count(roles) from users group by last_name;


				select name, count(sales) from customers group by name;


				select cars, avg(cost) "Total" from my_cars group by cars having avg(cost) > 10000;

Include or exclude grouped rows by using the HAVING clause

select model from cars group by model having color='blue';


				select * from users group by age having age > 35;

Updated: July 30, 2005; Joe Gakenheimer