Каждая < спецификация группировки > в < GROUP BY-спецификации > должна однозначно указывать на < имя столбца > или < значимое выражение > (их синонимы не допустимы), получаемое в результате выполнения предшествующей < FROM-спецификации > или < WHERE-спецификации >.

select make from auto where color='BLACK' group by make;
SELECT name, make, MAX(cylnders)
  FROM auto, person
 WHERE auto.personid=person.personid
 GROUP BY name, make;

select year+1900, model
  from auto group by year,model;

Опция PK задает группировку по первичному ключу таблицы. Это позволяет исключить повторение всех неагрегируемых столбцов записей выборки данных из конструкции GROUP BY.

Например, если в конструкции GROUP BY col1, col2, col3… столбец col1 является первичным ключом, то столбцы col2, col3… при группировке уже не учитываются, поэтому их можно заменить конструкцией PK.

Допускается синтаксис < имя таблицы >.PK, так и просто PK для случая одной таблицы.

create or replace table test (id int primary key, salary int, dept int);

insert into test values(1, 10, 1);
insert into test values(2, 15, 1);
insert into test values(3, 15, 2);
insert into test values(4, 20, 2);
insert into test values(5, 20, 3);

Нижеследующие запросы эквивалентны:

select id, salary*1000, 'department #' + to_char(dept)
  from  test
 group by id,salary,dept
having dept <  3;

select id, salary*1000, 'department #' + to_char(dept)
  from test group by   pk
having   dept <  3;

Если в таблице есть и первичный ключ, и столбец с именем "PK", то группировка GROUP BY PK будет производиться по первичному ключу.

В < значимом выражении > < спецификации группировки >, соответствующем GROUP BY, не должен содержаться < подзапрос >. Можно использовать конструкции < спецификация значения по условию > (CASE), < спецификация типа > (CAST), знаки операций, скалярные функции и логические выражения.

Примеры.
а)
create or replace table tst (i int, b boolean);
insert into tst values (1,TRUE);
insert into tst values (2,FALSE);
insert into tst values (1,TRUE);
insert into tst values (1,TRUE);

select i, count(b) from tst group by i,2 >1;
 I
 -
|          1|          3|
|          2|          1|

б) Определить название отдела в зависимости от его номера и сгруппировать полученные данные по названию отдела:
select dept_id, case dept_id when 1 then 'testing'
  when 2 then 'development'
  when 3 then 'directing'
  when 4 then 'management'
  else 'common department' end as name_of_department
  from tab_aggr
 group by dept_id, case dept_id when 1 then 'testing'
                   when 2 then 'development'
                   when 3 then 'directing'
                   when 4 then 'management'
                   else 'common department' end;

Результат выполнения примера:
 DEPT_ID     NAME_OF_DEPARTMENT
 -------     ------------------
|          1|testing           |
|          2|development       |
|          3|directing         |
|          4|management        |
|          5|common department |
|          6|common department |
|          7|common department |

В < списке выборки > можно указывать целиком < значимое выражение >, по которому делается группировка, или другие столбцы, используемые как аргументы агрегатных функций.

select round(weight/1000) as "Вес в тоннах", model
  from auto group by round(weight/1000), model;

Пользовательскую функцию, содержащую внутри себя < запросы выборки >, можно не указывать в < спецификации группировки >.

create or replace table t1( i int, j int );
insert into t1 values(1,4);
insert into t1 values(2,5);
insert into t1 values(2,6);
insert into t1 values(3,6);
insert into t1 values(3,7);

create or replace procedure test(in i int) result int for debug
declare var s cursor( i int );//
code
  open s for direct "select i from t1 where i="+itoa(i)+";";//
  fetch s;//
  return s.i;//
end;
Эти конструкции эквивалентны:
select i, test(i) from t1 group by i, test(i);
select i, test(i) from t1 group by i;
|          1|          1|
|          2|          2|
|          3|          3|
Аналогичные конструкции с пользовательскими функциями, не содержащими < запрос выборки >, недопустимы.
select i, max(5)  from t1 group by i, max(5); //не выполняется
select i, (select 5)  from t1 group by i, (select 5); // выполняется
select i, (select 5)  from t1 group by i; // выполняется

Операция GROUP BY запрещена для столбцов типа BLOB и EXTFILE.

Если < элементом группировки > является < SQL-параметр >, то должен явно указываться тип данных этого параметра.

select year+1900, :model (char (20))
  from auto group by year,:model (char (20));
FORD
|       1970|FORD                |
|       1971|FORD                |

В операции GROUP BY запрещается использование более чем одной функции с атрибутом DISTINCT, кроме случая дублирования одного и того же аргумента функции (например, в SELECT и в HAVING).

Правильная конструкция:

select round(avg( cylnders),0), count( model) from auto group by color;

Недопустимая конструкция (DISTINCT применяется для разных выражений):

select round(avg( distinct cylnders),0), count( distinct model) from auto group by  color;

Допустимая конструкция (DISTINCT применяется для одинаковых выражений):

select round(avg( distinct (cylnders+length(make))),0), count(distinct (cylnders+length(make))) from auto group by color;

На первом месте в списке < групп > должна быть непустая группа.

Недопустимая конструкция:

select make from auto group by grouping sets (),make;

Результат < GROUP BY-спецификации > – разбивка предшествующей < FROM-спецификации > или < WHERE-спецификации > на набор групп. Набор групп – это минимальное число таких групп, в каждой из которых все значения группируемых столбцов равны.

select model, count(*) from auto where year= 70 group by model;
| 124 SPORT COUPE | 6 |
| 1302S           | 3 |
| 1600            | 7 |
  …

Каждая строка полученной группы содержит одно и то же значение каждого группирующего столбца. Если к группе применяется < HAVING-спецификация > или < значимое выражение >, то ссылка на группирующий столбец является ссылкой на это значение.

Конструкция < группировка со сведением данных > указывает на то, что помимо стандартных строк, предоставленных предложением GROUP BY, в результирующий набор добавляются сводные (итоговые) строки. Сводная строка возвращается для всех возможных сочетаний групп и подгрупп в результирующем наборе. Имя столбца в сводной строке результирующего набора выводится как NULL-значение. Чтобы определить, представляют ли NULL-значения в результирующем наборе сводные значения или фактические данные, используется функция GROUPING.

Опция ROLLUP указывает на то, что помимо стандартных строк, предоставленных GROUP BY, в результирующий набор вводятся сводные строки.

Подсчитать количество автомобилей разного цвета:

select color, count(*)
  from auto
 group by color;
 COLOR
 -----
| BLACK         |        262 |
| BLUE          |        108 |
| BROWN         |         46 |
| GREEN         |         43 |
| GREY          |        104 |
| RED           |         52 |
| WHITE         |        323 |
| YELLOW        |         62 |

Подсчитать количество автомобилей разного цвета с выдачей сводной (итоговой) суммы можно двумя способами:

a) без конструкции ROLLUP:

select color, count(*)
  from auto
 group by color
 union
select 'Итого: ', count(*) from auto;
COLOR
 -----
| BLACK          |        262 |
| BLUE           |        108 |
| BROWN          |         46 |
| GREEN          |         43 |
| GREY           |        104 |
| RED            |         52 |
| WHITE          |        323 |
| YELLOW         |         62 |
| Итого:         |       1000 |

б) с использованием конструкции ROLLUP:

select color, count(*)
  from auto
 group by rollup(color);
COLOR
 -----
| BLACK          |        262 |
| BLUE           |        108 |
| BROWN          |         46 |
| GREEN          |         43 |
| GREY           |        104 |
| RED            |         52 |
| WHITE          |        323 |
| YELLOW         |         62 |
|                |       1000 |

Чтобы итоговая сумма имела название (например, 'Итого:'), надо применить функцию GROUPING.

Подсчитать количество автомобилей разного цвета с выдачей сводной (итоговой) суммы:

select case GROUPING(color) when 0 then color else 'Итого: '  end as color,
 count(*) from auto group by rollup(color);

COLOR                                  
-----                                  
|BLACK           |        262|
|BLUE            |        108|
|BROWN           |         46|
|GREEN           |         43|
|GREY            |        104|
|RED             |         52|
|WHITE           |        323|
|YELLOW          |         62|
|Итого:          |       1000| 

При указании опции ROLLUP группы обобщаются в иерархическом порядке, начиная с самого нижнего уровня в группе и заканчивая самым верхним. Иерархия группы определяется порядком, в каком заданы столбцы, по которым производится группирование. Изменение порядка столбцов, по которым производится группирование, может повлиять на количество строк в результирующем наборе.

Подсчитать количество автомобилей разного цвета, выпущенных разными производителями:

select make, color, count(*)
  from auto
 group by rollup(make,color);
MAKE             COLOR
 ----             -----
| ALPINE           | BLACK  |  1 |
| ALPINE           | GREY   |  3 |
| ALPINE           | WHITE  |  3 |
| AMERICAN MOTORS  | BLACK  | 23 |
| AMERICAN MOTORS  | BLUE   | 11 |
| AMERICAN MOTORS  | BROWN  |  6 |
| AMERICAN MOTORS  | GREEN  |  2 |
| AMERICAN MOTORS  | GREY   |  6 |
…

Опция CUBE означает, что помимо строк, перечисленных в GROUP BY, в результирующий набор должны включаться сводные (итоговые) строки для всех возможных сочетаний групп и подгрупп результирующего набора (в отличие от опции ROLLUP, которая возвращает итоговую сумму только по одной группе).

Количество сводных строк в результирующем наборе определяется по количеству столбцов, включенных в предложение GROUP BY. Каждый операнд (столбец) в предложении GROUP BY привязывается к группирующему NULL-значению, и группирование применяется ко всем остальным операндам (столбцам). Поскольку оператор CUBE возвращает все возможные сочетания групп и подгрупп, количество строк остается тем же, независимо от заданного порядка группирования столбцов.

Подсчитать количество автомобилей разного цвета, выпущенных разными производителями, с выдачей сводных данных по цветовой гамме:

select make, color, count(*)
  from auto
 group by cube(make,color);
----              -----
| ALPINE          | BLACK |  1 |
| ALPINE          | GREY  |  3 |
| ALPINE          | WHITE |  3 |
| AMERICAN MOTORS | BLACK | 23 |
| AMERICAN MOTORS | BLUE  | 11 |
  …
…

(сводные данные по первой группе)
|ALPINE          |      | 7  |
|AMERICAN MOTORS |      | 91 |
|BMW             |      | 10 |
|CHRYSLER        |      | 168|
|CITROEN         |      | 7  |
|DATSUN          |      | 10 |
|DE TOMASO       |      | 12 |
…

(сводные данные по второй группе)
|         |BLACK  |262 |
|         |BLUE   |108 |
|         |BROWN  |46  |
|         |GREEN  |43  |
|         |GREY   |104 |
|         |RED    |52  |
|         |WHITE  |323 |
|         |YELLOW  62
|         |        1000

Различия между опциями CUBE и ROLLUP:

Конструкция < группировка по заданным группам > (GROUPING SETS) позволяет группировать данные по любым произвольным группам или их комбинациям. Если группы или критерии группировки включают более одного столбца или выражения, то группы заключаются в скобки. Пустые скобки используются для обозначения единственной группы, которая охватывает всю таблицу.

Использование < GROUP BY-спецификация > с опцией GROUPING SETS эффективно в следующих случаях:

Алгоритм группировки данных:

< GROUP BY-спецификация > с опцией GROUPING SETS эквивалента полному объединению данных, получаемых из отдельных запросов для каждой группы.

Например:

create or replace table "Города"("Название" char(15), "Статус" char(4), "Население, чел." bigint);

insert into "Города" values ('Москва','рспб', 12000000), ('Воронеж', 'облс', 1000000), ('Борисоглебск', 'р-он', 400000), ('Семилуки', 'пгт', 120000), ('Курск', 'облс', 450000), ('Елец', 'р-он', 80000);

Получить список городов с их численностью населения и суммарное количество населения в городах соответствующего статуса.
Эти запросы эквивалентны:

SELECT "Название", "Статус", SUM("Население, чел.")
  FROM "Города"
 GROUP BY GROUPING SETS (("Название"), ("Статус"));

SELECT "Название", NULL as "Статус", SUM("Население, чел.")
  FROM "Города"
 GROUP BY "Название"
 UNION ALL
SELECT NULL as "Название", "Статус", SUM("Население, чел.")
  FROM "Города" GROUP BY "Статус";

    Название        |   Статус   |                 |
|   Борисоглебск    |            |      400000.0   |
|   Воронеж         |            |     1000000.0   |
|   Елец            |            |       80000.0   |
|   Курск           |            |      450000.0   |
|   Москва          |            |    12000000.0   |
|   Семилуки        |            |      120000.0   |
|                   |   облс     |     1450000.0   |
|                   |   пгт      |      120000.0   |
|                   |   р-он     |      480000.0   |
|                   |   рспб     |    12000000.0   |

GROUP BY a эквивалентно GROUP BY GROUPING SETS((a))

GROUP BY a,b,c  эквивалентно GROUP BY GROUPING SETS((a,b,c))

GROUP BY ROLLUP(a,b) эквивалентно GROUP BY GROUPING SETS((a,b),(a),() )

GROUP BY CUBE(a,b,c)эквивалентно
GROUP BY GROUPING SETS((a,b,c),
                         (a,b),
                         (a,c),
                         (a),
                         (b,c),
                         (b),
                         (c),
                         () )

GROUP BY a, ROLLUP(b,c) эквивалентно
GROUP BY GROUPING SETS((a,b,c)
                         (a,b)
                         (a) )

GROUP BY a, b, ROLLUP(c,d) эквивалентно
GROUP BY GROUPING SETS((a,b,c,d),
                         (a,b,c),
                         (a,b) )

GROUP BY ROLLUP(a), ROLLUP(b,c) эквивалентно
GROUP BY GROUPING SETS((a,b,c),
                         (a,b),
                         (a),
                         (b,c),
                         (b),
                         () )

GROUP BY ROLLUP(a), CUBE(b,c) эквивалентно
GROUP BY GROUPING SETS((a,b,c),
                         (a,b),
                         (a,c),
                         (a),
                         (b,c),
                         (b),
                         (c),
                         () )

GROUP BY CUBE(a,b), ROLLUP(c,d) эквивалентно
GROUP BY GROUPING SETS((a,b,c,d),
                         (a,b,c),
                         (a,b),
                         (a,c,d),
                         (a,c),
                         (a),
                         (b,c,d),
                         (b,c),
                         (b),
                         (c,d),
                         (c),
                         () )

GROUP BY a, ROLLUP(a,b) эквивалентно
GROUP BY GROUPING SETS((a,b),
                         (a))

Группировка по первичным ключам разных таблиц:

create or replace table test1(i int primary key, j int, ch char(10));
insert into test1 values(0, 1, 't0_1');
insert into test1 values(1, 1, 't1_1');
insert into test1 values(2, 1, 't1_2');
insert into test1 values(3, 3, 't1_3');

create or replace table test2(i int, j int, ch char(10), primary key(i,j));
insert into test2 values(1, 1, 't2_1');
insert into test2 values(1, 2, 't2_2');
insert into test2 values(2, 1, 't2_3');
insert into test2 values(3, 1, 't2_4');

select t1.i as t1i, t1.j as t1j, t2.i as t2i, t2.j as t2j, min(t1.ch) as mint1ch, max(t2.ch) as maxt2ch
  from test1 t1, test2 t2
 where t1.i=t2.i
 group by t1.pk, t2.pk
having min(t1.ch)  > 't1';

   T1I       T1J     T2I      T2J    MINT1CH  MAXT2CH
   ---       ---     ---      ---    -------  -------
|       1|       1|       1|       1|t1_1    |t2_1    |
|       1|       1|       1|       2|t1_1    |t2_2    |
|       2|       1|       2|       1|t1_2    |t2_3    |
|       3|       3|       3|       1|t1_3    |t2_4    |

Группировка данных по двум группам:

select model as "Модели автомобилей", color as "Окраска автомобилей"
  from auto
 group by grouping sets ((model),(color));

Модели автомобилей   Окраска автомобилей
 ------------------   -------------------
|124 SPORT COUPE     |                   |
|1275 GT             |                   |
|1302 S              |                   |
|1600                |                   |
…
|SM                  |                   |
|TR 6                |                   |
|XJ 6 4.2            |                   |
|                    |BLACK              |
|                    |BLUE               |
|                    |BROWN              |
|                    |GREEN              |
|                    |GREY               |
|                    |RED                |
|                    |WHITE              |
|                    |YELLOW             |

Использование функции GROIPING и конструкции GROUPING SETS:

select "Название", "Статус",
/* выдает битовую маску: первый бит – если значение агрегировано */
/* по всем "Городам", второй – если по всем "Статусам" */
  grouping ("Название","Статус")
  from "Города"
 group by
/* группировка идентична CUBE, но расписан список группирующих множеств */
   grouping sets (("Название","Статус"),("Название"),("Статус"),());

Название        Статус
 --------        ------
|Борисоглебск   |р-он  |    0|
|Воронеж        |облс  |    0|
|Елец           |р-он  |    0|
|Курск          |облс  |    0|
|Москва         |рспб  |    0|
|Семилуки       |пгт   |    0|
|Борисоглебск   |      |    1|
|Воронеж        |      |    1|
|Елец           |      |    1|
|Курск          |      |    1|
|Москва         |      |    1|
|Семилуки       |      |    1|
|               |облс  |    2|
|               |пгт   |    2|
|               |р-он  |    2|
|               |рспб  |    2|
|               |      |    3|