Group Functions Flashcards
Which group functions will consider NULL values?
COUNT(*) and GROUPING
Can custom functions work on grouped data?
No
If no GROUP BY clause is specified, the default grouping becomes ____
The entire result set
What can be used as arguments for ORDER BY
the identifier or the column position (number)
AVG
AVG([{DISTINCT | ALL}] n) where n is a numeric expression to be evaluated.
COUNT
COUNT({* | [DISTINCT | ALL] })
If DISTINCT not specified, defaults to ALL
* counts all rows in result set, including NULL
MAX
MAX([{DISTINCT | ALL}] )
Can be datetime, numeric or character.
Character returns highest based on character set.
MIN
MIN([{DISTINCT | ALL}] )
Can be datetime, numeric or character.
Character returns highest based on character set.
SUM
SUM([{DISTINCT | ALL}] )
MEDIAN
MEDIAN() Returns middle value of result set.
STDDEV
STDDEV([{DISTINCT | ALL}] ) Returns standard deviation of result set.
VARIANCE
VARIANCE([{DISTINCT | ALL}] ) Returns numerical variance of result set.
LISTAGG
LISTAGG( [col_name, ‘, ‘]) WITHIN GROUP ( ORDER BY )
Aggregates data from multiple rows into one row per group.
CORR
CORR(y, x) Returns coefficient of the correlation of a set of number pairs.
COVAR_POP
COVAR_POP(y, x) Returns population covariance of a set of number pairs. Covariance can return NULL.
COVAR_SAMP
COVAR_SAMP(y, x) Returns the sample covariance of a set of number pairs. Covariance can return NULL.
CUME_DIST
CUME_DIST() WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
where val_list is a comma-delimited list of expressions that evaluate to numeric constants and col_list is the comma-delimited list of column expressions.
Returns the cumulative distribution of a value in val_list within a distribution in col_list.
Result will be between 0 and 1.
DENSE_RANK
DENSE_RANK(val_list) WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
where val_list is a comma-delimited list of expressions that evaluate to numeric constants and col_list is the comma-delimited list of column expressions.
Returns the row’s rank within an ordered group, which are consecutive integers starting with 1 reflecting the number of unique values returned by the query.
GROUP_ID
GROUP_ID( )
No arguments
Requires GROUP BY
Returns a unique identifier for duplicate groups which is a value between 0 and i-1 where i is the number of duplicate groups.
GROUPING
GROUPING(x) Applicable to statements with a ROLLUP or CUBE clause. Returns 1 for summary rows and 0 for nonsummary.
PERCENT_RANK
PERCENT_RANK() WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
Returns the percent ranking of a value in val_list within a distribution in col_list in the range between 0 and 1. Can return 0 whereas CUME_DIST cannot.
PERCENTILE_CONT
PERCENTILE_CONT() WITHIN GROUP (ORDER BY col_list [ASC|DESC])
Assumes a continuous distribution and returns the interpolated value that would fall in percentile position x within the sorted group col_list.
PERCENTILE_DISC
Same as PERCENTILE_CONT but assumes discrete distribution and will return the middle-most of the values in the list.
RANK
RANK() WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
Like DENSE_RANK but will include duplicate values.
If there are three values tied for first place, DENSE_RANK would just list one of them as first and ignore the next two, putting the fourth in 2nd place. RANK on the other hand will list them all as in 1st place and place the 4th in 4th place.<»
STDDEV_POP
STDDEV_POP() Returns numeric population standard deviation of x.
STDDEV_SAMP
STDDEV_SAMP() Returns the numeric sample standard deviation of x.
VAR_POP
VAR_POP() Returns population variance of x.
VAR_SAMP
VAR_SAMP() Returns sample variance of x.
HAVING
HAVING() Works like WHERE but for GROUP functions (WHERE can’t use group functions). Comes after GROUP BY
ROLLUP
Used to return superaggregate rows. Will return an extra row of subtotals for each group. NULL values will be counted separately for each group and will have their own row unless using an NVL function.
CUBE
Creates subtotals for all possible combinations of grouping columns.
How many levels of nesting are allowed with group functions?
Any number for single-row within group, only one for group within group.
