Database Reference
In-Depth Information
There are a few standard rules to remember about the GROUP BY
clause:
The GROUP BY clause column list must include all columns in the
SELECT statement not affected by any aggregate functions.
The expression for the SELECT statement must include at least one
grouping function such as COUNT().
The GROUP BY clause cannot use the column positional specifica-
tion like the ORDER BY clause because the result set columns do
not exist when the GROUP BY clause is executed and do exist when
the ORDER BY clause is executed. The GROUP BY clause summa-
rizes rows for output, and the ORDER BY clause sorts the result set
of a query.
Note:
The GROUP BY clause is executed during query execution, and the
ORDER BY clause runs after retrieval and grouping of all rows. The
ORDER BY clause will always add performance overhead to a query.
Implicit or inherent sorting can often be executed in the WHERE and
GROUP BY clauses.
11.4.1
Grouping Rows
The example in Figure 11.2 showed how to apply an aggregate function to
all rows in a table, by summing up all amounts on the STUDIOTIME
table. Now we can take this a step further by breaking down the query in
Figure 11.2 into subset groups as in the next query. The result is shown in
Figure 11.11.
SELECT ARTIST_ID, SUM(AMOUNT_CHARGED), SUM(AMOUNT_PAID)
FROM STUDIOTIME
GROUP BY ARTIST_ID;
Now let's take the same query a little further, modify and beautify it.
The result is shown in Figure 11.12.
COLUMN ARTIST FORMAT A20 HEADING "Artist"
COLUMN OUTSTANDING FORMAT $999990.00 -
