User's Guide
   PART 1. Working with Databases
     CHAPTER 5. Summarizing, Grouping, and Sorting Query Results       

The GROUP BY clause: organizing query results into groups

The GROUP BY clause divides the output of a table into groups. You can GROUP BY one or more column names, or by the results of computed columns using numeric data types in an expression.

  Using GROUP BY with aggregate functions

A GROUP BY clause almost always appears in statements that include aggregate functions, in which case the aggregate produces a value for each group. These values are called vector aggregates. (Remember that a scalar aggregate is a single value produced by an aggregate function without a GROUP BY clause.)

Example 

  To list the average price of each kind of product:

1. Enter the following command:

   SELECT name, AVG(unit_price) as Price
   FROM product
   GROUP BY name

   name	Price
   Baseball Cap	9.500
   Shorts	15.000
   Sweatshirt	24.000
   Tee Shirt	12.333
   Visor	7.000

The summary values (vector aggregates) produced by SELECT statements with aggregates and a GROUP BY appear as columns in each row of the results. By contrast, the summary values (scalar aggregates) produced by queries with aggregates and no GROUP BY also appear as columns, but with only one row. For example:

SELECT AVG(unit_price)
FROM product

name	Price
Baseball Cap	9.500
Shorts	15.000
Sweatshirt	24.000
Tee Shirt	12.333
Visor	7.000

The summary values (vector aggregates) produced by SELECT statements with aggregates and a GROUP BY appear as columns in each row of the results. By contrast, the summary values (scalar aggregates) produced by queries with aggregates and no GROUP BY also appear as columns, but with only one row. For example:

SELECT AVG(unit_price)
FROM product

AVG(unit_price)
13.300000