Grouped Set Functions - the GROUP BY Clause
Normally, set functions return a single value, calculated from the set of all values in the column or expression.
If the SELECT statement includes a GROUP BY clause, set functions will be applied to groups of values. Columns used for GROUP BY do not have to be included in the SELECT list.
Find the most expensive NO SMOKING single room in each hotel.SELECT HOTELCODE, MAX(PRICE) AS EXPENSIVE FROM ROOM_PRICES WHERE ROOMTYPE = 'NSSGLB' OR ROOMTYPE = 'NSSGLS' GROUP BY HOTELCODE;
Restrictions when Using GROUP BY
Using a GROUP BY clause places some restrictions on the SELECT statement:
- Only constants, columns used in the GROUP BY clause, and columns used in set functions may be included in the SELECT list
- A column used in the GROUP BY clause may not be used in a set function.
How many hotels are there in each city?SELECT CITY, COUNT(HOTELCODE) FROM HOTEL GROUP BY CITY;
In a statement with column references in the SELECT list, all columns not used in set functions must be used as grouping columns.
For grouping purposes, NULL values are regarded as equivalent. Thus for the example table:
The following statement:SELECT SAMPLE, COUNT(*) AS NUMBER ... GROUP BY SAMPLE;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40