Example:
SELECT city, FIXED (AVG(account),7,2)
FROM customer
GROUP BY city
ORDER BY city
The query determines the average account for each city (AVG). As the second column of the result table has no longer a predefined name, the system gives the default heading EXPRESSION1 to it. GROUP BY arranges the table in groups of rows with the same city name and produces one result row for each group. ORDER BY should be used for sorted output of the group results. GROUP BY collects the results by groups; but it does not necessarily sort them.
One of the functions that are applied to a whole column of a temporary result table must be written before all the columns, except 'city'. (These functions are here also called 'set functions'.) 'city' does not need a function specification, because each element of the group has the same value for 'city'.
AVG is a function that is applied to a whole column, like MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE. Section Groups with Conditions: HAVING contains a more detailed explanation of these functions.
GROUP BY usually produces a group for each different value of the column to be grouped.
Grouping can be done according to several columns. To begin with, groups are formed according to the first criterion. Then each of these groups is arranged according to the next criterion, and so on.
If GROUP BY is used, it must follow FROM and WHERE and precede ORDER BY.
Show the minimum, the average, and the maximum account of all customers for each city:
SELECT city, MIN(account) min_account,
FIXED (AVG(account),7,2) avg_account,
MAX(account) max_account
FROM customer
GROUP BY city
New headings were defined for the calculated result columns.
Show the minimum, the average, and the maximum account of all customers for each city, except New York.
SELECT city, MIN(account) min_account,
FIXED (AVG(account),7,2) avg_account,
MAX(account) max_account
FROM customer
WHERE city <> 'New York'
GROUP BY city
Show the number of customers and the total of their accounts for each city:
SELECT city, COUNT(*) number,
FIXED (AVG(account),7,2) avg_account,
SUM(account) sum_account
FROM customer
GROUP BY city
