Aggregate functions are SQL functions designed to allow you to summarize data from multiple rows of a table or view. These aggregate functions, many of which are useful for data warehouse applications, are only valid for use in SQL statements. Unlike the other built-in functions, they cannot be directly invoked from a PL/SQL expression (see Chapter 7 for more information about PL/SQL programming). Table 5-1 lists the available aggregate functions.
|AVG||Returns the average value of a column over a set of rows|
|COUNT||Returns the number of non-NULL values in a column over a set of rows|
|GROUPING||Allows you to insert subtotal (superaggregate) rows into a query that uses Oracle's ROLLUP and CUBE extensions|
|MAX||Returns the maximum value of a column over a set of rows|
|MIN||Returns the minimum value of a column over a set of rows|
|STDDEV||Returns the standard deviation of all values in a column for a set of rows|
|SUM||Sums the values in a column for a set of rows|
|VARIANCE||Returns the variance of values in a column for a set of rows|
When used with an aggregate function, the GROUP BY clause causes Oracle to report the result for each distinct value of a column. The following example uses GROUP BY to break out the count of objects by database user:
SQL> SELECT owner, COUNT(object_name) 2 FROM dba_objects 3 GROUP BY owner; OWNER COUNT(OBJECT_NAME) ------------------------------ ------------------ ...