Using GROUP BY with multiple columns

You can list more than one expression in the GROUP BY clause—that is, you can group a table by any combination of expressions.

The following query lists the average price of products, grouped first by name and then by size:

SELECT Name, Size, AVG( UnitPrice )
   FROM Products
   GROUP BY Name, Size;
Name Size AVG(Products.UnitPrice)
Tee Shirt Small 9
Tee Shirt Medium 14
Tee Shirt One size fits all 14
Baseball Cap One size fits all 9.5
... ... ...
Columns in GROUP BY that are not in the select list

A Sybase extension to the SQL/92 standard that is supported by both Adaptive Server Enterprise and SQL Anywhere is to allow expressions to the GROUP BY clause that are not in the select list. For example, the following query lists the number of contacts in each city:

SELECT State, COUNT( ID )
   FROM Contacts
   GROUP BY State, City;