12-dars. Agregat funksiyalar (COUNT, SUM, AVG, MIN, MAX)

Aggregate Functions — jadvaldagi bir nechta qatorlar ustida hisob-kitob qilib, bitta natija qaytaradigan funksiyalar.

Masalan, kompaniyada 100 ta order bo'lsa, quyidagi savollarga javob topishimiz mumkin:

• Jami nechta order bor?

• Jami savdo qancha?

• O'rtacha order qancha?

• Eng katta order qancha?

• Eng kichik order qancha?

Bunday vazifalarda Agregat funksiyalar ishlatiladi.

Agregat funksiyalar

Biz bu darsda 5 ta asosiy Agregat funksiyalarni o'rganamiz:

COUNT()

COUNT() — qatorlar yoki qiymatlar sonini hisoblaydi.

Masalan:

Bizda nechta order bor?

SELECT 
   COUNT(*) AS Order_Count
FROM Orders;

COUNT(*) nima degani?

* bu yerda barcha qatorlarni hisobla degan ma'noni anglatadi.

COUNT(column)

COUNT() ichida ma'lum bir ustunni ham ko'rsatish mumkin.

SELECT
    COUNT(CustomerName) AS Customer_Count
FROM Orders;

Bu CustomerName ustunidagi NULL bo'lmagan qiymatlarni sanaydi.

Shuning uchun:

COUNT(*) → qatorlarni sanaydi

COUNT(column) → column'dagi NULL bo'lmagan qiymatlarni sanaydi

Bu NULL mavzusida o'rganganlarimiz bilan ham bog'lanadi.

SUM()

SUM() — sonli ustundagi qiymatlarning yig'indisini hisoblaydi.

Masalan:

Barcha orderlarning jami savdosi qancha?

SELECT
    SUM(Sales) AS Total_Sales
FROM Orders;

Bizning datasetimizda:

1200 + 800 + 450 + ... + 350

AVG()

AVG() — sonli qiymatlarning o'rtachasini hisoblaydi.

Masalan:

Bitta orderning o'rtacha savdo qiymati qancha?

SELECT
    AVG(Sales) AS Average_Sales
FROM Orders;

MAX()

MAX() — eng katta qiymatni topadi.

Eng katta order qancha?

SELECT
    MAX(Sales) AS Max_Sales
FROM Orders;

MIN()

MIN() — eng kichik qiymatni topadi.

Eng kichik order qancha?

SELECT
    MIN(Sales) AS Min_Sales
FROM Orders;

Barcha agregat funksiyalar birgalikda

Endi bitta query orqali umumiy hisobot olamiz:

SELECT
    COUNT(*) AS Order_Count,
    SUM(Sales) AS Total_Sales,
    AVG(Sales) AS Average_Sales,
    MAX(Sales) AS Max_Sales,
    MIN(Sales) AS Min_Sales
FROM Orders;

Natija:

Bu yerda barcha orderlar bitta guruh sifatida hisoblandi.

GROUP BY

Endi darsning muhim qismiga o'tamiz.

GROUP BY — ma'lumotlarni ma'lum bir ustun bo'yicha guruhlarga ajratish uchun ishlatiladi.

Masalan, biz:

Barcha orderlar soni nechta?

deb so'rasak:

SELECT COUNT(*)
FROM Orders;

Natija:

15

Lekin:

Har bir davlat bo'yicha nechta order bor?

desak, ma'lumotni Country bo'yicha guruhlashimiz kerak.

Buning uchun GROUP BY ishlatiladi.

GROUP BY + COUNT()

SELECT
    Country,
    COUNT(*) AS Order_Count
FROM Orders
GROUP BY Country;

Bu query qanday ishladi?

SQL ma'lumotlarni:

Germany
USA
Uzbekistan

guruhlariga ajratdi. Keyin har bir guruhdagi orderlarni sanadi.

GROUP BY + SUM()

Endi:

Har bir davlat bo'yicha jami savdo qancha?

SELECT
    Country,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Country;

Bu yerda SUM() hisoblayapti, GROUP BY esa qaysi guruhlar uchun alohida hisoblashni belgilayapti.

GROUP BY + AVG()

Har bir davlat bo'yicha o'rtacha order qancha?

SELECT
    Country,
    AVG(Sales) AS Average_Sales
FROM Orders
GROUP BY Country;

GROUP BY + MAX()

Har bir davlatdagi eng katta order qancha?

SELECT
    Country,
    MAX(Sales) AS Max_Sales
FROM Orders
GROUP BY Country;

GROUP BY + MIN()

Har bir davlatdagi eng kichik order qancha?

SELECT
    Country,
    MIN(Sales) AS Min_Sales
FROM Orders
GROUP BY Country;

Funksiyalarni eslab qolish

GROUP BY

"Har bir ... bo'yicha" degan savol bo'lsa:

→ GROUP BY

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_12.sql (2.7 KB)

⬇️ Uyga vazifa .sql fayli: 📎 dars_12_uy_ishi.sql (4.3 KB)