Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


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;
Result:

 HOTELCODE 
  EXPENSIVE
 LAP 
  800 
 SKY 
  870 
 STG 
  680 
 WIND 
  1410 
 WINS 
  1370 

Restrictions when Using GROUP BY

Using a GROUP BY clause places some restrictions on the SELECT statement:

How many hotels are there in each city?
 SELECT  CITY, COUNT(HOTELCODE)
 FROM    HOTEL
 GROUP BY CITY;
 
Result:

 CITY

 COPENHAGEN
 1
 GOTHENBURG
 1
 London
 1
 STOCKHOLM
 2
 UPPSALA
 1

Column References

In a statement with column references in the SELECT list, all columns not used in set functions must be used as grouping columns.

Null Values

For grouping purposes, NULL values are regarded as equivalent. Thus for the example table:

 SAMPLE
 1.0
 2.0
 2.0
 2.0
 3.0
 3.0
 4.0
 5.0
 -
 -

The following statement:
 SELECT  SAMPLE, COUNT(*) AS NUMBER
 ...
 GROUP BY SAMPLE;
 
Results in:

 SAMPLE
 NUMBER
 1.0
 1
 2.0
 3
 3.0
 2
 4.0
 1
 5.0
 1
 -
 2



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX