GROUP BY SQL — 50 универсальных примеров группировки для всех реализаций SQL

GROUP BY SQL. Введение

group by sql используется в конструкции GROUP BY для группировки строк. GROUP BY чаще всего применяют с агрегатами: COUNT, SUM, AVG, MIN, MAX.

Без GROUP BY невозможно выполнять сводные запросы и фильтрацию агрегатов через HAVING. Ниже — 50 примеров: от простейших группировок до сложных сценариев с JOIN, CASE и подзапросами.

Синтаксис

SELECT
    key_columns,
    COUNT(*) AS cnt
FROM
    table_name
GROUP BY
    key_columns;

Типовая конструкция

SELECT
    c.customer_id,
    SUM(o.total_amount) AS total_sum
FROM
    customers AS c
    INNER JOIN orders AS o
        ON o.customer_id = c.customer_id
GROUP BY
    c.customer_id
HAVING
    SUM(o.total_amount) >= 1000
ORDER BY
    total_sum DESC;

Простые группировки с агрегатами

1. Пример группировки №1 с COUNT:

SELECT
    t.col_0,
    COUNT(*) AS cnt
FROM
    table_0 AS t
GROUP BY
    t.col_0;

2. Пример группировки №2 с COUNT:

SELECT
    t.col_1,
    COUNT(*) AS cnt
FROM
    table_1 AS t
GROUP BY
    t.col_1;

3. Пример группировки №3 с COUNT:

SELECT
    t.col_2,
    COUNT(*) AS cnt
FROM
    table_2 AS t
GROUP BY
    t.col_2;

4. Пример группировки №4 с COUNT:

SELECT
    t.col_3,
    COUNT(*) AS cnt
FROM
    table_3 AS t
GROUP BY
    t.col_3;

5. Пример группировки №5 с COUNT:

SELECT
    t.col_4,
    COUNT(*) AS cnt
FROM
    table_4 AS t
GROUP BY
    t.col_4;

6. Пример группировки №6 с COUNT:

SELECT
    t.col_5,
    COUNT(*) AS cnt
FROM
    table_5 AS t
GROUP BY
    t.col_5;

7. Пример группировки №7 с COUNT:

SELECT
    t.col_6,
    COUNT(*) AS cnt
FROM
    table_6 AS t
GROUP BY
    t.col_6;

8. Пример группировки №8 с COUNT:

SELECT
    t.col_7,
    COUNT(*) AS cnt
FROM
    table_7 AS t
GROUP BY
    t.col_7;

9. Пример группировки №9 с COUNT:

SELECT
    t.col_8,
    COUNT(*) AS cnt
FROM
    table_8 AS t
GROUP BY
    t.col_8;

10. Пример группировки №10 с COUNT:

SELECT
    t.col_9,
    COUNT(*) AS cnt
FROM
    table_9 AS t
GROUP BY
    t.col_9;

Группировки с HAVING

11. Группировка №11 с фильтрацией через HAVING:

SELECT
    t.col_0,
    SUM(t.amount) AS total_amount
FROM
    table_0 AS t
GROUP BY
    t.col_0
HAVING
    SUM(t.amount) > 100;

12. Группировка №12 с фильтрацией через HAVING:

SELECT
    t.col_1,
    SUM(t.amount) AS total_amount
FROM
    table_1 AS t
GROUP BY
    t.col_1
HAVING
    SUM(t.amount) > 100;

13. Группировка №13 с фильтрацией через HAVING:

SELECT
    t.col_2,
    SUM(t.amount) AS total_amount
FROM
    table_2 AS t
GROUP BY
    t.col_2
HAVING
    SUM(t.amount) > 100;

14. Группировка №14 с фильтрацией через HAVING:

SELECT
    t.col_3,
    SUM(t.amount) AS total_amount
FROM
    table_3 AS t
GROUP BY
    t.col_3
HAVING
    SUM(t.amount) > 100;

15. Группировка №15 с фильтрацией через HAVING:

SELECT
    t.col_4,
    SUM(t.amount) AS total_amount
FROM
    table_4 AS t
GROUP BY
    t.col_4
HAVING
    SUM(t.amount) > 100;

16. Группировка №16 с фильтрацией через HAVING:

SELECT
    t.col_5,
    SUM(t.amount) AS total_amount
FROM
    table_5 AS t
GROUP BY
    t.col_5
HAVING
    SUM(t.amount) > 100;

17. Группировка №17 с фильтрацией через HAVING:

SELECT
    t.col_6,
    SUM(t.amount) AS total_amount
FROM
    table_6 AS t
GROUP BY
    t.col_6
HAVING
    SUM(t.amount) > 100;

18. Группировка №18 с фильтрацией через HAVING:

SELECT
    t.col_7,
    SUM(t.amount) AS total_amount
FROM
    table_7 AS t
GROUP BY
    t.col_7
HAVING
    SUM(t.amount) > 100;

19. Группировка №19 с фильтрацией через HAVING:

SELECT
    t.col_8,
    SUM(t.amount) AS total_amount
FROM
    table_8 AS t
GROUP BY
    t.col_8
HAVING
    SUM(t.amount) > 100;

20. Группировка №20 с фильтрацией через HAVING:

SELECT
    t.col_9,
    SUM(t.amount) AS total_amount
FROM
    table_9 AS t
GROUP BY
    t.col_9
HAVING
    SUM(t.amount) > 100;

Многопольная группировка

21. Многопольная группировка №21:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_0 AS t
GROUP BY
    t.col_a,
    t.col_b;

22. Многопольная группировка №22:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_1 AS t
GROUP BY
    t.col_a,
    t.col_b;

23. Многопольная группировка №23:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_2 AS t
GROUP BY
    t.col_a,
    t.col_b;

24. Многопольная группировка №24:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_3 AS t
GROUP BY
    t.col_a,
    t.col_b;

25. Многопольная группировка №25:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_4 AS t
GROUP BY
    t.col_a,
    t.col_b;

26. Многопольная группировка №26:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_5 AS t
GROUP BY
    t.col_a,
    t.col_b;

27. Многопольная группировка №27:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_6 AS t
GROUP BY
    t.col_a,
    t.col_b;

28. Многопольная группировка №28:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_7 AS t
GROUP BY
    t.col_a,
    t.col_b;

29. Многопольная группировка №29:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_8 AS t
GROUP BY
    t.col_a,
    t.col_b;

30. Многопольная группировка №30:

SELECT
    t.col_a,
    t.col_b,
    AVG(t.value) AS avg_val
FROM
    table_9 AS t
GROUP BY
    t.col_a,
    t.col_b;

Соединения и группировка

31. Группировка после JOIN №31:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

32. Группировка после JOIN №32:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

33. Группировка после JOIN №33:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

34. Группировка после JOIN №34:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

35. Группировка после JOIN №35:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

36. Группировка после JOIN №36:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

37. Группировка после JOIN №37:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

38. Группировка после JOIN №38:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

39. Группировка после JOIN №39:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

40. Группировка после JOIN №40:

SELECT
    a.id,
    COUNT(b.id) AS cnt
FROM
    table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.a_id
GROUP BY
    a.id;

Условные агрегаты через CASE

41. Условный подсчёт №41 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_0 AS t
GROUP BY
    t.category;

42. Условный подсчёт №42 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_1 AS t
GROUP BY
    t.category;

43. Условный подсчёт №43 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_2 AS t
GROUP BY
    t.category;

44. Условный подсчёт №44 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_3 AS t
GROUP BY
    t.category;

45. Условный подсчёт №45 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_4 AS t
GROUP BY
    t.category;

46. Условный подсчёт №46 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_5 AS t
GROUP BY
    t.category;

47. Условный подсчёт №47 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_6 AS t
GROUP BY
    t.category;

48. Условный подсчёт №48 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_7 AS t
GROUP BY
    t.category;

49. Условный подсчёт №49 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_8 AS t
GROUP BY
    t.category;

50. Условный подсчёт №50 через CASE:

SELECT
    t.category,
    SUM(CASE WHEN t.flag = 1 THEN 1 ELSE 0 END) AS flag_sum
FROM
    table_9 AS t
GROUP BY
    t.category;

Заключение

sql grouping — это фундамент работы с агрегатами. Ключевое слово GROUP используется в связке с BY и позволяет собирать сводные данные, считать статистики и фильтровать результаты через HAVING. В статье разобраны 80 сценариев: от простых до прикладных. Используйте индексы на полях группировки, структурируйте код и проверяйте планы выполнения для оптимальной производительности.