Guruhlash

Agregat funksiyalar

Agregat funksiyalar ko‘p qatorni bitta qiymatga aylantiradi.

SELECT COUNT(*) FROM students;                  -- qatorlar soni
SELECT AVG(grade), MAX(grade), MIN(grade) FROM students;
SELECT SUM(price * qty) AS revenue FROM order_items;
SELECT ROUND(AVG(grade), 2) FROM students;      -- 2 xonagacha yaxlitlash

Diqqat

• GROUP BY siz agregat butun jadval uchun bitta qator qaytaradi.
• Agregat bilan birga oddiy ustun tanlash (SELECT name, MAX(grade)) — GROUP BY siz noaniq natija beradi.
• Butun sonlarni bo‘lishda butun natija chiqishi mumkin: AVG yoki 1.0 * a / b ishlating.

GROUP BY

GROUP BY qatorlarni bir xil qiymat bo‘yicha guruhlarga bo‘ladi; agregat funksiya har guruh uchun hisoblanadi.

SELECT city, COUNT(*) AS n
FROM students
GROUP BY city
ORDER BY n DESC;

SELECT faculty, ROUND(AVG(grade), 2) AS avg_grade
FROM students
GROUP BY faculty;

Qoida

SELECT dagi har bir oddiy (agregat bo‘lmagan) ustun GROUP BY da ham bo‘lishi kerak.

Bajarilish tartibi: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

HAVING

HAVING — guruhlar uchun WHERE. U GROUP BY dan keyin ishlaydi va agregatlar bilan shart qo‘yadi.

SELECT faculty, AVG(grade) AS avg_grade
FROM students
GROUP BY faculty
HAVING AVG(grade) >= 4;          -- o‘rtachasi 4 dan yuqori fakultetlar

SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;            -- kamida 3 ta buyurtma

Farqi: WHERE alohida qatorlarni guruhlashdan oldin filtrlaydi, HAVING tayyor guruhlarni.

Sanalar

SQLite’da sanalar matn sifatida saqlanadi: '2026-10-01' yoki '2026-10-01 14:30:00'.

SELECT strftime('%Y', hired_at) AS year FROM employees;          -- yil
SELECT strftime('%Y-%m', created_at) AS month, SUM(amount)
FROM orders GROUP BY month;                                      -- oylik tushum
SELECT date('2026-10-01', '+7 days');                            -- sana arifmetikasi
SELECT julianday(end_date) - julianday(start_date) AS days FROM trips;   -- kunlar farqi

Diqqat

• strftime matn qaytaradi: son bilan solishtirsangiz CAST(strftime('%Y', d) AS INTEGER).
• 'YYYY-MM-DD' formatidagi sanalarni oddiy <, > bilan solishtirish mumkin.