Agregat funksiyalar va GROUP BY

SELECT
    COUNT(*)              AS talabalar_soni,
    AVG(ortacha_baho)     AS ortacha,
    MIN(ortacha_baho)     AS eng_past,
    MAX(ortacha_baho)     AS eng_yuqori,
    SUM(ortacha_baho)     AS yigindi
FROM talabalar;

SELECT
    COUNT(*)                AS barcha_qatorlar,
    COUNT(telefon)          AS telefoni_borlar,
    COUNT(DISTINCT shahar)  AS noyob_shaharlar
FROM talabalar;

SELECT
    shahar,
    COUNT(*)                     AS talabalar_soni,
    ROUND(AVG(ortacha_baho), 2)  AS ortacha_baho
FROM talabalar
GROUP BY shahar
ORDER BY talabalar_soni DESC;

SELECT
    shahar,
    COUNT(*) AS soni,
    GROUP_CONCAT(ism ORDER BY ism SEPARATOR ', ') AS talabalar
FROM talabalar
GROUP BY shahar;

SELECT
    shahar,
    guruh_id,
    COUNT(*)                    AS soni,
    ROUND(AVG(ortacha_baho), 2) AS ortacha
FROM talabalar
GROUP BY shahar, guruh_id
ORDER BY shahar, guruh_id;

-- 1 tadan ko'p talabasi bor shaharlar
SELECT shahar, COUNT(*) AS soni
FROM talabalar
GROUP BY shahar
HAVING COUNT(*) > 1;

SELECT
    shahar,
    COUNT(*)          AS soni,
    SUM(ortacha_baho) AS yigindi
FROM talabalar
GROUP BY shahar WITH ROLLUP;

-- NULL o'rniga "JAMI" yozish
SELECT
    IFNULL(shahar, 'JAMI') AS shahar,
    COUNT(*) AS soni
FROM talabalar
GROUP BY shahar WITH ROLLUP;

-- Guruhlar bo'yicha statistika
SELECT
    g.nomi                          AS guruh,
    COUNT(t.id)                     AS talabalar,
    ROUND(AVG(t.ortacha_baho), 2)   AS ortacha,
    MAX(t.ortacha_baho)             AS eng_yuqori,
    MIN(t.ortacha_baho)             AS eng_past
FROM guruhlar g
LEFT JOIN talabalar t ON t.guruh_id = g.id
GROUP BY g.id, g.nomi
ORDER BY ortacha DESC;

-- Baho darajalari bo'yicha taqsimot
SELECT
    CASE
        WHEN ortacha_baho >= 4.5 THEN 'A''lo (4.5+)'
        WHEN ortacha_baho >= 3.5 THEN 'Yaxshi (3.5-4.5)'
        ELSE 'Qoniqarli (3.5 gacha)'
    END                             AS daraja,
    COUNT(*)                        AS soni,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM talabalar), 1) AS foiz
FROM talabalar
GROUP BY daraja
ORDER BY soni DESC;

-- Oylik savdo hisoboti
SELECT
    DATE_FORMAT(yaratilgan, '%Y-%m')  AS oy,
    COUNT(*)                          AS buyurtmalar,
    SUM(summa)                        AS jami_savdo,
    ROUND(AVG(summa))                 AS ortacha_chek,
    MAX(summa)                        AS eng_katta_chek
FROM buyurtmalar
WHERE yaratilgan >= '2026-01-01'
GROUP BY oy
ORDER BY oy;

-- Shartli sanash - juda foydali naqsh
SELECT
    shahar,
    COUNT(*)                                        AS jami,
    SUM(CASE WHEN ortacha_baho >= 4.5 THEN 1 ELSE 0 END) AS alochilar,
    SUM(CASE WHEN faolmi = FALSE THEN 1 ELSE 0 END)      AS nofaollar,
    SUM(telefon IS NOT NULL)                        AS telefoni_borlar
FROM talabalar
GROUP BY shahar;

SELECT   shahar, COUNT(*) AS soni, AVG(ortacha_baho) AS ortacha
FROM     talabalar                    -- 1. qaysi jadval
WHERE    faolmi = TRUE                -- 2. qaysi qatorlar
GROUP BY shahar                       -- 3. qanday guruhlash
HAVING   COUNT(*) >= 2                -- 4. qaysi guruhlar
ORDER BY ortacha DESC                 -- 5. qanday saralash
LIMIT    10;                          -- 6. nechta