GROUP BY asoslari — guruhlab hisoblash

Hisobotlarning deyarli hammasi bitta savolga borib taqaladi: "har bir … bo'yicha nechta / qancha?". Har bir shahar bo'yicha buyurtmalar soni, har bir kurs bo'yicha o'rtacha baho, har bir oy bo'yicha tushum. Bularning hammasi GROUP BY bilan yechiladi.

Jadval: sales

Bitta guruh — bitta qator

SELECT region,
       COUNT(*)    AS sales_count,
       SUM(amount) AS total,
       AVG(amount) AS avg_amount
FROM sales
GROUP BY region;

GROUP BY region jadvalni ikki "qop"ga ajratdi. Agregat funksiyalar har bir qop ichida ishladi va har bir qopdan bitta qator chiqdi.

Diqqat qiling: Farg'onada 2 ta sotuv bor, lekin o'rtacha 2500. AVG va SUM NULL qiymatlarni hisobga olmaydi — o'rtacha faqat bitta qiymatdan olindi.

COUNT(*) va COUNT(ustun) farqi

• COUNT(*) — guruhdagi barcha qatorlar soni.
• COUNT(amount) — amount NULL bo'lmagan qatorlar soni.

SELECT region, COUNT(*) AS rows_count, COUNT(amount) AS with_amount
FROM sales
GROUP BY region;

Takrorlanmas qiymatlar soni kerak bo'lsa — COUNT(DISTINCT product).

Bir nechta ustun bo'yicha guruhlash

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product
ORDER BY region, total DESC;

Endi guruh — (region, product) jufti: "Toshkent + Telefon" alohida, "Toshkent + Noutbuk" alohida.

Eng ko'p uchraydigan xato: guruhlanmagan ustun

SELECT region, product, SUM(amount)
FROM sales
GROUP BY region;

PostgreSQL xato beradi: column "sales.product" must appear in the GROUP BY clause or be used in an aggregate function. Toshkent guruhida ikki xil mahsulot bor — bitta qatorga qaysi birini yozishni baza bilmaydi.

Qoida oddiy: SELECT dagi har bir ustun yoki GROUP BY da bo'lsin, yoki agregat ichida.

SELECT region, COUNT(DISTINCT product) AS products, SUM(amount) AS total
FROM sales
GROUP BY region;

Xulosa

• GROUP BY — "har bir … bo'yicha" savollari uchun; har bir guruhdan bitta qator.
• SUM, AVG, COUNT(ustun) — NULL ni o'tkazib yuboradi; COUNT(*) — hamma qatorni sanaydi.
• SELECT dagi ustun yoki GROUP BY da, yoki agregat ichida bo'lishi shart.
• Guruhlarni filtrlash kerak bo'lsa — HAVING (alohida darslikda).