Group BY Clause¶
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
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
clause is restricted: only the attributes specified in the
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
In a group-by view, derived attribute functions can also appear in the
SELECT clause, although only applied to aggregation fields or
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 aggregation functions that exist in Virtual
DataPort are enumerated in the appendix 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).
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;