SQL GROUP BY va agregat funksiyalar

Agregat funksiyalar bir nechta qatordan yagona natija hisoblab beradi.

Agregat funksiyalar

SELECT 
    COUNT(*) AS jami_talabalar,
    AVG(yosh) AS ortacha_yosh,
    MIN(yosh) AS eng_yosh,
    MAX(yosh) AS eng_katta,
    SUM(to'lov) AS jami_tulangan
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'rtacha baho
SELECT kurs_id, AVG(baho) AS ortacha_baho
FROM baholar
GROUP BY kurs_id;

HAVING — guruhlarni filtrlash

WHERE agregat funksiyalar bilan ishlamaydi, HAVING ishlatiladi:

-- 5 dan ko'p talabasi bor shaharlar
SELECT shahar, COUNT(*) AS soni
FROM talabalar
GROUP BY shahar
HAVING COUNT(*) > 5
ORDER BY soni DESC;

WHERE va HAVING birga

SELECT kurs, AVG(baho) AS ortacha
FROM baholar
WHERE sana >= '2024-01-01'     -- avval filtrlash
GROUP BY kurs
HAVING AVG(baho) > 70           -- guruhlarni filtrlash
ORDER BY ortacha DESC;

ROLLUP bilan jami

SELECT shahar, kurs, COUNT(*) AS soni
FROM talabalar
GROUP BY ROLLUP(shahar, kurs);