This chapter deals with three optional points of a SELECT statement: the
GROUP BY clause, the set functions {AVG, COUNT, MAX, MIN,
SUM, EVERY, ANY, SOME, GROUPING}, and the HAVING
clause. Often these things appear together: the common factor is summaries, or
amalgams, of Columns — with groups, rather than with
details. We group together where values are equal. For example, confronted with
the detail list {Smith Smith Smith Jones Jones}, we could summarize it to be:
"three Smiths, two Joneses". Such a summary is known in SQL as
a grouped Table.
-
GROUP BY Clause
The GROUP BY clause is an optional portion of the SELECT statement. It defi... -
Rules For Grouping Columns
In the GROUP BY clause: Each Column in a GROUP BY clause must unambiguously... -
The Single-Value Rule
The rationale for this rule is as follows. Suppose you have a list of citie... -
Grouping by Expressions
We said earlier that, while you can use "GROUP BY Column list" in your SELE... -
New Syntax
Until now, all of our examples have shown GROUP BY followed by one or more ... -
Set Functions
The SQL set functions — more commonly called aggregate functions — are AVG,... -
DISTINCT Set Functions
Since DISTINCT <set function> is usually a slow process, it's worthwhile to... -
Set Functions and the "ignore NULLs" policy
All the set functions ignore NULLs (the COUNT(*) function looks like an exc... -
Set functions in Subqueries
Who makes more than the average salary? What is the cheapest book? That's t... -
Retrieval with a Set Function
Here's some examples of set functions, using the sample database we defined... -
HAVING Clause
What departments have four employees? In which branches is the smallest boo... -
Views of Groups
It's a straightforward exercise to make a View which is based on a grouping... -
Dialects
The older (pre-1992) DBMSs had these restrictions on grouping: Any set func...
