DevGuru Jet SQL GROUP BY Clause

Quick References

Features

Partners


			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.