O'Reilly logo

Oracle SQL: the Essential Reference by David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required