CLAUSE: GROUP BY
SELECT fieldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist]
[HAVING search_criteria]
The optional GROUP BY clause combines into a single record all records
that have identical values in a particular field or combination
of fields.
You can use up to 10 fields to group records, with the order of field
names determining the group levels from highest to lowest.
A HAVING clause may also be used in conjunction with a
GROUP-BY clause to further restrict the search criteria.
All fields containing a NULL are considered to have a value and
will be grouped along with the fields containing non-NULL values.
The following example returns a list
of the different products in the Product
field of Suppliers:
SELECT Product FROM Suppliers
GROUP BY Product;
You can also use any of the nine aggregate
functions (AVG, COUNT, MIN, MAX, STDEV, STDEVP, SUM, VAR, VARP)
to include statisical values for each record.
Any field containing a NULL value will be ignored in the
statistical calculations performed by the aggregate functions.
Expanding on the previous example, the following returns a
list of different products and a field called ProdCount that counts
the number of times each product occurs in the Product field of
Suppliers (i.e. how many suppliers supply it):
SELECT Product, COUNT(Product) AS ProdCount FROM Suppliers
GROUP BY Product;
You can also include a WHERE clause
to apply certain criteria before values are grouped. The next
example returns a list of all different products that are blue, and
the sum cost for one of each:
SELECT Item, Sum(UnitPrice) AS TotalCost FROM Products
WHERE Color = 'blue'
GROUP BY Item;
If a field appears in the SELECT field list, it must appear in
either the GROUP BY clause or as an argument to one of the SQL aggregate
functions.
Copyright 2000 by Infinite Software Solutions, Inc.
Trademark Information
|