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 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information