COUNT, SUM, AVG, MIN, MAX

«Nechta talaba bor?», «o‘rtacha ball qancha?», «eng qimmat mahsulot narxi?» — bunday savollar ko‘p qatordan bitta son chiqaradi. Buning uchun agregat funksiyalar bor.

Beshta asosiy funksiya

SELECT COUNT(*) AS students FROM students;

SELECT MIN(score), MAX(score), ROUND(AVG(score), 1) FROM grades;

WHERE bilan birga

Avval qatorlar tanlanadi, keyin hisoblanadi:

SELECT COUNT(*) FROM students WHERE funding = 'grant';

COUNT(*), COUNT(ustun) va COUNT(DISTINCT …)

• COUNT(*) — hamma qatorlar;
• COUNT(phone) — faqat phone bo‘sh bo‘lmagan qatorlar;
• COUNT(DISTINCT city) — nechta har xil shahar.

SELECT COUNT(*) AS all_rows, COUNT(phone) AS with_phone, COUNT(DISTINCT city) AS cities FROM students;

Agregatlar NULL qiymatlarni hisobga olmaydi: AVG(scholarship) faqat stipendiya oladiganlar o‘rtachasini beradi.

Muhim qoida

Agregat va oddiy ustunni shunchaki yonma-yon yozib bo‘lmaydi:

SELECT city, COUNT(*) FROM students;   -- xato!

Baza qaysi city ni ko‘rsatishni bilmaydi — natija bitta qator, shahar esa ko‘p. Har bir shahar uchun alohida sanash kerak bo‘lsa — GROUP BY kerak. Bu keyingi dars.

Esda qoldiring

• COUNT, SUM, AVG, MIN, MAX — ko‘p qatordan bitta qiymat.
• Agregatlar NULL ni tashlab ketadi; COUNT(*) esa hamma qatorni sanaydi.
• Oddiy ustun va agregat birga kerak bo‘lsa — GROUP BY.