Oracle SQL: the Essential Reference

5.1. Aggregate Functions

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.

Table 5-1. Oracle's Aggregate Functions

Function	Description
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

5.1.1. GROUP BY

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) ------------------------------ ------------------ ...