GROUP BY va HAVING

GROUP BY qatorlarni bir xil qiymat bo‘yicha guruhlarga ajratadi va har bir guruh uchun alohida hisoblaydi. «Har bir shahardan nechta talaba?», «har bir kategoriyada o‘rtacha narx?» — hammasi shu.

Qanday ishlaydi

SELECT city, COUNT(*) AS students
FROM students
GROUP BY city
ORDER BY students DESC;

Baza talabalarni shaharlar bo‘yicha «qoziqlarga» ajratadi, keyin har bir qoziqni sanaydi. Natijada har bir shahar — bitta qator.

Qoida

SELECT dagi har bir ustun yoki GROUP BY da bo‘lishi, yoki agregat ichida turishi kerak:

SELECT course, funding, COUNT(*) FROM students GROUP BY course, funding ORDER BY course, funding;

PostgreSQL bu qoidani doim qat’iy tekshiradi: qoidani buzsangiz — «must appear in the GROUP BY clause» xatosi.

HAVING — guruhlar uchun shart

WHERE qatorlarni guruhlashdan oldin filtrlaydi, HAVING esa guruhlarni keyin:

SELECT city, COUNT(*) AS students
FROM students
GROUP BY city
HAVING COUNT(*) > 25
ORDER BY students DESC;

Ikkalasi birga:

SELECT city, COUNT(*) AS grant_students
FROM students
WHERE funding = 'grant'
GROUP BY city
HAVING COUNT(*) >= 10;

Bajarilish tartibi

So‘rov shu tartibda yoziladi, baza ham deyarli shu tartibda bajaradi:

1. FROM — qaysi jadval;
2. WHERE — qaysi qatorlar;
3. GROUP BY — guruhlash;
4. HAVING — qaysi guruhlar;
5. SELECT — nimani ko‘rsatish;
6. ORDER BY, LIMIT — tartib va cheklov.

Shuning uchun WHERE ichida COUNT(*) ishlatib bo‘lmaydi — u paytda guruhlar hali yo‘q.

Esda qoldiring

• GROUP BY — guruhlab hisoblash; SELECT dagi oddiy ustunlar GROUP BY da bo‘lsin.
• WHERE — qatorlar uchun, HAVING — guruhlar uchun.
• Tartib: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.