GROUP BY va HAVING — guruhlash va filtrlash

~20 daq · MySQL · DML — Ma'lumot Boshqarish

Muammodan boshlaylik

O'tgan darsda butun jadval bo'yicha bitta javob oldik: "jami 7 ta mahsulot". Endi savol boshqacha:

  • Har bir toifada nechta mahsulot bor?
  • Har bir shaharda nechta mijoz?
  • Har bir mijoz qancha pul sarflagan?
  • Har oy tushum qancha?

"Har bir ... uchun" — bu guruhlash. Jadval qatorlari guruhlarga bo'linadi va har bir guruh uchun alohida aggregate hisoblanadi.

GROUP BY

SELECT toifa_id, COUNT(*) AS soni
FROM mahsulotlar
GROUP BY toifa_id;

+----------+------+
| toifa_id | soni |
+----------+------+
|     NULL |    1 |
|        1 |    2 |
|        2 |    2 |
|        3 |    2 |
+----------+------+

MySQL nima qildi:

1. Qatorlarni toifa_id bo'yicha guruhladi:
   toifa_id=1: [iPhone 15, Samsung Galaxy A55]
   toifa_id=2: [MacBook Air M3, Lenovo IdeaPad]
   toifa_id=3: [AirPods Pro, Qopqoq]
   NULL:       [Sovg'a kartasi]
2. Har bir guruh uchun COUNT(*) hisobladi
3. Har bir guruhdan bitta qator qaytardi

NULL lar bitta alohida guruhga tushadi.

Bir nechta aggregate

SELECT
    toifa_id,
    COUNT(*)          AS turlar,
    SUM(soni)         AS jami_dona,
    MIN(narx)         AS eng_arzon,
    MAX(narx)         AS eng_qimmat,
    ROUND(AVG(narx))  AS ortacha
FROM mahsulotlar
GROUP BY toifa_id;

Asosiy qoida

GROUP BY bor so'rovda SELECT da faqat ikki xil narsa bo'lishi mumkin:

  1. GROUP BY dagi ustunlar
  2. Aggregate funksiyalar
-- ❌
SELECT toifa_id, nom, COUNT(*) FROM mahsulotlar GROUP BY toifa_id;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause...

Bitta toifada 2 ta mahsulot — qaysi nom ni ko'rsatish kerak? Noaniq. Shuning uchun xato.

Guruh ichidagi qiymatlarni ro'yxat qilib ko'rsatish kerak bo'lsa:

SELECT toifa_id, GROUP_CONCAT(nom ORDER BY narx SEPARATOR ', ') AS mahsulotlar
FROM mahsulotlar
GROUP BY toifa_id;

+----------+-------------------------------------+
| toifa_id | mahsulotlar                         |
+----------+-------------------------------------+
|     NULL | Sovg'a kartasi                      |
|        1 | Samsung Galaxy A55, iPhone 15       |
|        2 | Lenovo IdeaPad, MacBook Air M3      |
|        3 | Qopqoq, AirPods Pro                 |
+----------+-------------------------------------+

JOIN bilan — nomlar bilan guruhlash

toifa_id = 1 dan ko'ra "Telefonlar" tushunarliroq:

SELECT t.nom AS toifa, COUNT(m.id) AS soni
FROM toifalar t
LEFT JOIN mahsulotlar m ON m.toifa_id = t.id
GROUP BY t.id, t.nom;

Har bir mijoz qancha sarflagan:

SELECT
    m.ism,
    COUNT(DISTINCT b.id)    AS buyurtmalar,
    SUM(q.soni * q.narx)    AS jami
FROM mijozlar m
JOIN buyurtmalar b        ON b.mijoz_id = m.id
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
WHERE b.holat <> 'bekor'
GROUP BY m.id, m.ism
ORDER BY jami DESC;

+-----------------+-------------+----------+
| ism             | buyurtmalar | jami     |
+-----------------+-------------+----------+
| Malika Rahimova |           2 | 21360000 |
| Aziz Karimov    |           2 |  8140000 |
+-----------------+-------------+----------+

COUNT(DISTINCT b.id) — nima uchun DISTINCT? JOIN dan keyin har bir buyurtma qatorlari soniga qarab takrorlanadi (1-buyurtmada 2 ta qator). Oddiy COUNT buyurtmalarni emas, qatorlarni sanab qo'yardi.

JOIN lar 15-16 darslarda batafsil.

Bir nechta ustun bo'yicha guruhlash

SELECT shahar, holat, COUNT(*) AS soni
FROM buyurtmalar b
JOIN mijozlar m ON m.id = b.mijoz_id
GROUP BY shahar, holat
ORDER BY shahar, holat;

Har bir (shahar, holat) juftligi — alohida guruh.

Sana bo'yicha — oylik hisobot

SELECT
    DATE_FORMAT(b.yaratilgan, '%Y-%m') AS oy,
    COUNT(DISTINCT b.id)               AS buyurtmalar,
    SUM(q.soni * q.narx)               AS tushum
FROM buyurtmalar b
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
WHERE b.holat <> 'bekor'
GROUP BY oy
ORDER BY oy;

+---------+-------------+----------+
| oy      | buyurtmalar | tushum   |
+---------+-------------+----------+
| 2026-09 |           1 |  5040000 |
| 2026-10 |           2 | 23100000 |
| 2026-11 |           1 |  1360000 |
+---------+-------------+----------+

MySQL GROUP BY da SELECT dagi taxallusni (oy) ishlatishga ruxsat beradi.

HAVING — guruhlarni filtrlash

"Kamida 2 ta mahsulotli toifalar" — bu shartni qayerga yozish kerak?

SELECT toifa_id, COUNT(*) AS soni
FROM mahsulotlar
WHERE COUNT(*) >= 2          -- ❌ ERROR 1111: Invalid use of group function
GROUP BY toifa_id;

WHERE guruhlashdan oldin ishlaydi — u paytda COUNT(*) hali hisoblanmagan. Guruhlardan keyin filtrlash uchun — HAVING:

SELECT toifa_id, COUNT(*) AS soni
FROM mahsulotlar
GROUP BY toifa_id
HAVING COUNT(*) >= 2;

WHERE va HAVING farqi

WHERE HAVING
Qachon ishlaydi Guruhlashdan oldin Guruhlashdan keyin
Nimani filtrlaydi Alohida qatorlarni Guruhlarni
Aggregate funksiyalar ❌ ✅

Ikkalasi birga:

-- Yetkazilgan buyurtmalardan 10 mln dan ko'p sarflagan mijozlar
SELECT m.ism, SUM(q.soni * q.narx) AS jami
FROM mijozlar m
JOIN buyurtmalar b        ON b.mijoz_id = m.id
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
WHERE b.holat = 'yetkazildi'           -- 1. qatorlar: faqat yetkazilganlar
GROUP BY m.id, m.ism                   -- 2. mijozlar bo'yicha guruhlash
HAVING SUM(q.soni * q.narx) > 10000000 -- 3. guruhlar: faqat katta xaridorlar
ORDER BY jami DESC;

Qoida: qator darajasida filtrlash mumkin bo'lgan shartni WHERE ga yozing — u guruhlanadigan qatorlar sonini kamaytiradi va so'rov tezroq ishlaydi. HAVING — faqat aggregate shartlar uchun.

To'liq bajarilish tartibi

1. FROM / JOIN   — jadvallar
2. WHERE         — qatorlarni filtrlash
3. GROUP BY      — guruhlash
4. HAVING        — guruhlarni filtrlash
5. SELECT        — ustunlar va aggregate lar
6. DISTINCT
7. ORDER BY      — saralash
8. LIMIT         — kesish

Bu tartibni bilsangiz, ko'p xatolarning sababini darhol tushunasiz.

WITH ROLLUP — oraliq va umumiy jami

SELECT toifa_id, SUM(soni) AS jami
FROM mahsulotlar
GROUP BY toifa_id WITH ROLLUP;

+----------+------+
| toifa_id | jami |
+----------+------+
|     NULL |  100 |
|        1 |   16 |
|        2 |    8 |
|        3 |   95 |
|     NULL |  219 |   ← umumiy jami
+----------+------+

Oxirgi qator — barcha guruhlar yig'indisi. Hisobotlar uchun qulay. Asl NULL guruhini umumiy jamidan ajratish uchun — GROUPING(toifa_id) funksiyasi (1 — rollup qatori).

Nima uchun shunday?

Nima uchun ONLY_FULL_GROUP_BY rejimi guruhlanmagan ustunni taqiqlaydi?

Eski MySQL bunga ruxsat berardi va guruhdan istalgan qatorning qiymatini qaytarardi. Natija ko'pincha "to'g'ri ko'rinardi" — test ma'lumotlarida bitta qator bo'lgani uchun. Production da esa jimgina noto'g'ri raqamlar chiqardi. MySQL 5.7 da bu rejim standart yoqildi — va millionlab eski so'rovlar birdaniga xato bera boshladi. Bu og'riqli edi, lekin to'g'ri qaror: noaniq natija qaytargandan ko'ra xato bergan yaxshi.

Xulosa

Eslab qoling:

  • GROUP BY ustun — har bir qiymat uchun alohida guruh, aggregate har biri uchun
  • SELECT da faqat GROUP BY ustunlari va aggregate funksiyalar
  • GROUP_CONCAT — guruh qiymatlarini bitta qatorga
  • JOIN dan keyin COUNT(DISTINCT id) — takrorlanishni hisobga oling
  • WHERE — guruhlashdan oldin (qatorlar), HAVING — keyin (guruhlar)
  • Mumkin bo'lgan shartni WHERE ga yozing — tezroq
  • WITH ROLLUP — umumiy jami qatori

Keyingi dars

15-dars: INNER JOIN — ikkita jadval birlashtirish