Grouping commands in SQL

In Section 1.2.4 we have seen how aggregate functions can be used to compute a single value for a column. Often applications require grouping rows that have certain properties and then applying an aggregate function on one column for each group separately. For this, SQL provides the clause group by <group column(s)>. This clause appears after the where clause and must refer to columns of tables listed in the from clause.
select <column(s)>
from <table(s)>
where <condition>
group by <group column(s)>
[having <group condition(s)>];
Those rows retrieved by the selected clause that have the same value(s) for <group column(s)>
are grouped. Aggregations specified in the select clause are then applied to each group separately.
It is important that only those columns that appear in the <group column(s)> clause
can be listed without an aggregate function in the select clause !
Example: For each department, we want to retrieve the minimum and maximum salary.
select DEPTNO, min(SAL), max(SAL)
from EMP
group by DEPTNO;
Rows from the table EMP are grouped such that all rows in a group have the same department
number. The aggregate functions are then applied to each such group. We thus get the following
query result:
16
DEPTNO MIN(SAL) MAX(SAL)
10 1300 5000
20 800 3000
30 950 2850
Rows to form a group can be restricted in the where clause. For example, if we add the
condition where JOB = ’CLERK’, only respective rows build a group. The query then would
retrieve the minimum and maximum salary of all clerks for each department. Note that is not allowed to specify any other column than DEPTNO without an aggregate function in the select clause since this is the only column listed in the group by clause (is it also easy to see that other columns would not make any sense).
Once groups have been formed, certain groups can be eliminated based on their properties,
e.g., if a group contains less than three rows. This type of condition is specified using the
having clause. As for the select clause also in a having clause only <group column(s)> and
aggregations can be used.
Example: Retrieve the minimum and maximum salary of clerks for each department having
more than three clerks.
select DEPTNO, min(SAL), max(SAL)
from EMP
where JOB = ’CLERK’
group by DEPTNO
having count() > 3;
Note that it is even possible to specify a subquery in a having clause. In the above query, for example, instead of the constant 3, a subquery can be specified.
A query containing a group by clause is processed in the following way:
1. Select all rows that satisfy the condition specified in the where clause.
2. From these rows form groups according to the group by clause.
3. Discard all groups that do not satisfy the condition in the having clause.
4. Apply aggregate functions to each group.
5. Retrieve values for the columns and aggregations listed in the select clause.