Каждая <​спецификация группировки​> в <​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|