Java Reference
In-Depth Information
Description
Count
Average Cost
Cookies
3
1.2866
Soda
3
0.63
Notice that the name given to the "Count" column was quoted, since COUNT is a
SQL keyword.
Because the GROUP BY clause combines all records with identical values in one
column into a single record, each of the column names in the SELECT clause must
be either a column specified in the GROUP BY clause or a column function such as
COUNT() or AVG(). This means that you can't SELECT a list of individual customers
by name and then count them as a group using GROUP BY. However, you can group
on more than one column, just as you can use more than one column with the
ORDER BY clause.
Every column name specified in the SELECT statement must also be mentioned in
the GROUP BY clause. Not mentioning the column names in both places gives you
an error. The GROUP BY clause returns a row for each unique combination column
in the GROUP BY clause.
Aggregate Functions
Aggregate functions
return a single value from an operation on a column of data. This
differentiates them from the arithmetic, logical, and character operators discussed
earlier in this chapter, which operate on individual data elements.
Most Relational Database Management Systems support the following aggregate
functions:
SUM Sum of column values
AVG Average of column values
STDEV Standard deviation of column values
COUNT Count of rows in column
MAX Maximum value in column
MIN Minimum value in column
Aggregate functions are used to provide statistical or summary information about
groups of data elements. These groups may be created specifically using the
GROUP BY clause, or the aggregate functions may be applied to the default group,
which is the entire result set.
Here's a good practical example of the use of most of the common aggregate
functions:
