Contents Index The GROUP BY clause: organizing query results into groups Understanding GROUP BY

ASA SQL User's Guide
  Summarizing, Grouping and Sorting Query Results
    The GROUP BY clause: organizing query results into groups

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 

The following query lists the average price of each kind of product:

SELECT name, AVG(unit_price) AS Price
FROM product
GROUP BY name
name Price
Tee Shirt 12.333333333
Baseball Cap 9.5
Visor 7
Sweatshirt 24
... ...

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(product.unit_price)
13.3

Contents Index The GROUP BY clause: organizing query results into groups Understanding GROUP BY