The records requested in a SELECT command may be grouped by a common attribute value. This is done with a GROUP BY clause that names an attribute on which grouping should occur. The output will be sorted according to the grouped attribute.
Grouping may be nested by giving a list of attributes in the GROUP BY clause. The primary grouping is done on the first attribute in the list, then within each group, records are grouped on the second attribute in the list, and so on. The maximum number of attributes in the list used for grouping is 10.
Grouping is often used with the functions COUNT, AVG, MAX, MIN and SUM. This allows functions to be applied to specific groups, rather than to all records at once. Applying functions to groups may change the format in which retrieved data is displayed. Where the result of a function is displayed under the group, the display defaults (e.g., column width and precision) do not apply, since they only apply to values that appear in columns.
Example
To see loans grouped by name, use:
SELECT name, date, amount FROM loans GROUP BY name;
This produces:
name date amount
Jones 7 February 1990 $33.95
3 April 1990 $25.00
12 August 1990 $300.00
Kilroy 16 February 1990 $250.00
Mosca 2 February 1990 $150.00
4 May 1990 $200.00
Peterson 6 June 1990 $50.00
Scarlatti 9 September 1990 $150.00
14 October 1990 $125.00
Wladislaw 27 February 1990 $55.00
12 May 1990 $55.00
25 June 1990 $75.00
Note that a sort is automatically done on the attribute name. Also, duplicate names in the group are omitted. You can have duplicate names printed by setting the Empress system variable MSQLSELKEEPDUPLICATE.
If you change Jones' third loan with an UPDATE command:
UPDATE loans SET amount = 25
WHERE name = "Jones" AND amount = 300;
Then issue the command:
SELECT name, date, amount FROM loans
GROUP BY name, amount;
the output will look like:
name date amount
Jones 3 April 1990 $25.00
12 August 1990
7 February 1990 $33.95
Kilroy 16 February 1990 $250.00
Mosca 2 February 1990 $150.00
4 May 1990 $200.00
Peterson 6 June 1990 $50.00
Scarlatti 14 October 1990 $125.00
9 September 1990 $150.00
Wladislaw 27 February 1990 $55.00
12 May 1990
25 June 1990 $75.00
Note the sorting on name and amount in this example.
COUNT can be used to count within explicit groups. Grouping loans by name and counting within each group is done with:
SELECT name, COUNT FROM loans GROUP BY name;
which gives:
name COUNT(*) Jones 3 Kilroy 1 Mosca 2 Peterson 1 Scarlatti 2 Wladislaw 3
Here the COUNT function is displayed with an attribute appearing in the GROUP BY list. The results of the function appear in a column.
If the COUNT function is selected along with some other attribute (one not appearing in the GROUP BY list), the function result does not appear in a column but under each group. For example:
SELECT name, amount, COUNT FROM loans GROUP BY name;
Returns:
name amount
Jones $33.95
$25.00
$300.00
COUNT(*) = 3
Kilroy $250.00
COUNT(*) = 1
Mosca $150.00
$200.00
COUNT(*) = 2
Peterson $50.00
COUNT(*) = 1
Scarlatti $150.00
$125.00
COUNT (*) = 2
Wladislaw $55.00
$25.00
$75.00
COUNT(*) = 3
Functions can be used to find the sum, average, maximum and minimum values of attributes over explicit groups. Grouping loans by name and summing within each group is done with:
SELECT name, SUM (amount) FROM loans GROUP BY name;
which gives:
name SUM (amount) Jones 358.95 Kilroy 250.00 Mosca 150.00 Peterson 50.00 Scarlatti 275.00 Wladislaw 155.00
Here the function is displayed with an attribute appearing in the GROUP BY list. The results of the function appear in a column.
If the function is selected along with some other attribute (one not appearing in the GROUP BY list), the function result does not appear in a column but under each group. For example:
SELECT name, amount, SUM (amount) FROM loans
GROUP BY name;
This returns:
name amount
Jones $33.95
$25.00
$300.00
SUM(amount) = 358.95
Kilroy $250.00
SUM(amount) = 250.00
Mosca $150.00
$200.00
SUM(amount) = 350.00
Peterson $50.00
SUM(amount) = 50.00
Scarlatti $150.00
$125.00
SUM(amount) = 275.00
Wladislaw $55.00
$25.00
$75.00
SUM(amount) = 155.00