# CHAPTER 11: GROUP BY Clause

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

1. 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`.

2. 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.

3. `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.

4. 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

5. 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.

6. 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