JetSQL » Clauses » GROUP BY
- Syntax:
- SELECT fieldlist
FROM table
[ WHERE criteria ]
GROUP BY grouping_expression
[ HAVING criteria ] - fieldlist
- The list of fields that are to be retrieved from the table.
- table
- The name of the table from which the information is being retrieved.
- criteria
- The condition(s) that dictate which rows are to be retrieved.
- grouping_expression
- The parameter that specifies a set of columns on which to group matching data.
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.
Examples
Code:
SELECT Product FROM Suppliers GROUP BY Product;
Output:
Product
Desktop
Laptop
Mouse
Network Card
Hard Drive
Software
Book
Accessory
(8 row(s) affected)
Explanation:
This example returns a list of the different products in the 'Product' field from the 'Suppliers' table.
Language(s): MS SQL Server
Code:
SELECT Product, COUNT(Supplier) AS SupCount
FROM Suppliers
GROUP BY Product;
Output:
Product
SupCount
Desktop
25
Laptop
19
Mouse
33
Network Card
3
Hard Drive
1
Software
4
Book
6
Accessory
7
(8 row(s) affected)
Explanation:
You can also use any of the nine aggregate functions (AVG, COUNT, MIN, MAX, STDEV, STDEVP, SUM, VAR, VARP) to include statistical values for each record. Expanding on the previous example, this query returns a list of different products and a field called 'SupCount' that counts the number of suppliers that supply each product.
Language(s): MS SQL Server
Code:
SELECT Item, Sum(UnitPrice) AS TotalCost
FROM Products
WHERE Color = 'blue'
GROUP BY Item;
Output:
Item
TotalCost
Dell Axim X30
3627.00
Dell Inspiron 300m
19200.00
Intellisense Mouse
400.00
(3 row(s) affected)
Explanation:
The WHERE clause can also be used in conjunction with the GROUP BY clause to apply certain criteria before values are grouped. This example returns a list of all different products that are blue, and the total value of all such items.
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.
Language(s): MS SQL Server
See Also:
