Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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 number of rows in each category within the FORMATS table:
 SELECT category_id, COUNT(*)
 FROM formats
 GROUP BY category_id;
Returns:

CATEGORY_ID
1
4
2
4
3
2

Restrictions When Using GROUP BY

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

Only constants, columns listed in the GROUP BY clause, and columns used as arguments to set functions may be included in the SELECT list.

A derived table can be used to overcome this restriction.

Find the number of released items, grouped by year, month and format:
 select y, m, format, count(*)
 from
 (
     select extract(year from release_date) as y,
            extract(month from release_date) as m,
            format
     from product_details
 )
 group by y, m, format;

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

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


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX