SQL agregat funksiyalar va GROUP BY

Agregat funksiyalar — ko'p qatorlardan bitta natija hisoblaydi.

Asosiy agregat funksiyalar

-- Jami soni
SELECT COUNT(*) FROM talabalar;
SELECT COUNT(DISTINCT shahar) FROM talabalar;

-- Yig'indi
SELECT SUM(maosh) FROM xodimlar;

-- O'rtacha
SELECT AVG(ball) FROM talabalar;

-- Minimum va maksimum
SELECT MIN(yosh), MAX(yosh) FROM talabalar;

-- Hammasi birga
SELECT
    COUNT(*) AS jami,
    AVG(ball) AS o'rta_ball,
    MIN(ball) AS eng_past,
    MAX(ball) AS eng_yuqori,
    SUM(ball) AS umumiy
FROM talabalar;

GROUP BY

-- Shahar bo'yicha talabalar soni
SELECT shahar, COUNT(*) AS soni
FROM talabalar
GROUP BY shahar
ORDER BY soni DESC;

-- Kurs bo'yicha o'rta ball
SELECT kurs, AVG(ball) AS o'rta_ball
FROM talabalar
GROUP BY kurs;

-- Bir necha ustun bo'yicha guruhlash
SELECT shahar, kurs, COUNT(*) AS soni, AVG(ball) AS o'rta
FROM talabalar
GROUP BY shahar, kurs;

HAVING — GROUP BY filtri

-- 10 dan ko'p talabasi bo'lgan shaharlar
SELECT shahar, COUNT(*) AS soni
FROM talabalar
GROUP BY shahar
HAVING COUNT(*) > 10;

-- O'rta bali 75 dan yuqori kurslar
SELECT kurs, AVG(ball) AS o'rta_ball
FROM talabalar
GROUP BY kurs
HAVING AVG(ball) > 75
ORDER BY o'rta_ball DESC;

WHERE vs HAVING

-- WHERE - guruhlashdan OLDIN filtrlaydi
SELECT kurs, AVG(ball)
FROM talabalar
WHERE yosh > 18        -- avval filtr
GROUP BY kurs
HAVING AVG(ball) > 70; -- keyin guruhlaydi

-- To'g'ri tartib: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

ROLLUP

-- Oraliq yig'indilar bilan
SELECT shahar, kurs, SUM(ball)
FROM talabalar
GROUP BY ROLLUP(shahar, kurs);