In addition to creating queries that display individual data rows, you can create aggregate queries—queries that summarize information from multiple rows. For example, you can create a query that averages the contents of a price column. You can create the average for all rows in a table, or for groups of related rows — for example, all the rows that apply to a particular customer.
In this topic you can find information about summarizing and grouping information using queries.
Using summary queries, you can:
orders table, you can find the total of the order_total column to discover the sales total for a day.customers table, you can find out how many customers are in the state of California.invoices table, you can find the highest sale made this month.You can create summary queries on a table as a whole, or you can summarize subsets of a table by subdividing the table into groups.
To summarize all the data in a table, you create an aggregate query that involves a function such as SUM( ) or AVG( ). When you run the query, the result set contains a single row with the summary information. For example, you can calculate the total price of all books in the titles table by creating a query that sums the contents of the price column. The resulting query output might look like this:
The corresponding SQL statement might look like this:
SELECT SUM(price) total_price
FROM titles
You can use the following aggregate functions:
| Aggregate function | Description |
| AVG(expr) | Average of the values in a column. The column can contain only numeric data. |
| COUNT(expr), COUNT(*) | A count of the values in a column (if you specify a column name as expr) or of all rows in a table or group (if you specify *). COUNT(expr) ignores null values, but COUNT(*) includes them in the count. |
| MAX(expr) | Highest value in a column (last value alphabetically for text data types). Ignores null values. |
| MIN(expr) | Lowest value in a column (first value alphabetically for text data types). Ignores null values. |
| SUM(expr) | Total of values in a column. The column can contain only numeric data. |
Note Oracle supports additional aggregate functions. For details, see Query Designer Considerations for Oracle Databases.
When you use an aggregate function, by default the summary information includes all specified rows. In some instances, a result set includes non-unique rows. You can filter out non-unique rows by using the DISTINCT option of an aggregate function.
You can combine aggregate functions with other expressions to calculate other summary values. For details, see Summarizing Values Using Custom Expressions.
To create summary values for groups of rows, you create a query containing a GROUP BY clause. All the rows that contain the same group value are summarized into one row of the result set. The result set contains as many rows as there are unique values in the column you choose to group. If you group by multiple columns, the result set contains unique values for each combination of grouping columns you specify.
For example, you can query a titles table to find out the average price of a book for each publisher. To do so, you group the pub_id column so that all the rows for a specific publisher are summarized. The result set might look like this:
The corresponding SQL statement might look like this:
SELECT pub_id, AVG(price) AveragePrice
FROM titles
GROUP BY pub_id