Group BY Clause¶
The GROUP BY clause allows grouping the results of a query by the
values of the attributes of the view and/or expressions, generating a
row for each group. The attributes and expressions with which the group
by operation is performed are specified in the GROUP BY clause.
If the query does not have the GROUP BY clause, but its SELECT
clause uses aggregation functions, all the results obtained by the
SELECT statement would form one single group.
When a query has the GROUP BY clause, the content of the SELECT
clause is restricted: only the attributes specified in the GROUP BY
clause can be specified in it. The other attributes of the view can be
used only as parameters of aggregation functions. When an aggregation
function is specified in the SELECT clause, you should indicate and
alias for the new attribute. Otherwise the Server generates an alias
automatically.
In a group-by view, derived attribute functions can also appear in the
SELECT clause, although only applied to aggregation fields or
functions.
Use of Aggregation Functions¶
An aggregation function is applied to the tuples belonging to a group
resulting from a GROUP BY operation and calculates an aggregated
value from same. The section Aggregation Functions lists the aggregation functions.
The aggregation functions follow the general syntax of the predefined functions (see section Syntax Conventions), but only the name of the attribute subject to alteration is admitted as a parameter (nested functions are not admitted either).
The modifiers ALL and DISTINCT can also be specified.
One exception is the COUNT() aggregation function that can receive
as a parameter the special character * to indicate that it
should return the number of tuples that belong to each group.
For example, given a relation emp representing the employees of a
company that contains an attribute department which indicates to
which department each employee belongs, to obtain the different
departments together with the number of employees that belong to each
one of them, the following query would be executed:
SELECT count(*) AS numOfWorkers, department
FROM emp
GROUP BY department;
Or, using the alias of the field:
SELECT count(*) AS numOfWorkers, department AS dept_name
FROM emp
GROUP BY dept_name;