1-mavzu. Ma’lumotlar bazasi obyektlarini yaratish

Min va Max
Min va Max funksiyalari mos ravishda ustun uchun minimal va maksimal qiymatlarni qaytaradi. Masalan, mahsulotlar orasida minimal narxni topaylik:
SELECT MIN(Price) FROM Products
Maksimal qiymatni hisoblash
SELECT MAX(Price) FROM Products

Ushbu funksiyalar, hisoblashda NULL qiymatlarini inobatga olmaydi.

Sum
Sum funksiyasi ustundagi qiymatlar yig'indisini hisoblab chiqadi. Masalan, mahsulotlarning umumiy sonini hisoblab chiqaylik:
SELECT SUM(ProductCount) FROM Products

Shuningdek, ustun nomi o'rniga hisoblanadigan ifoda uzatilishi mumkin. Masalan, mavjud bo'lgan barcha tovarlarning umumiy narxini topaylik:
SELECT SUM(ProductCount * Price) FROM Products


All va Distinct
Boshlang’ich holda, yuqoridagi barcha beshta funksiya natijani hisoblash uchun barcha qatorlarni ko`rib chiqadi. Ammo ustunda takrorlanadigan qiymatlar bo'lishi mumkin. Agar takroriy ma`lumotlar qiymatlari to'plamidan tashqari, faqat unikal qiymatlar bo'yicha hisob-kitoblarni amalga oshirish kerak bo'lsa, u holda DISTINCT operatoridan foydalaniladi.
SELECT AVG(DISTINCT ProductCount) AS Average_Price FROM Products

Boshlang’ich holda, DISTINCT o'rniga barcha qatorlarni tanlaydigan ALL operatoridan foydalaniladi:

SELECT AVG(ALL ProductCount) AS Average_Price FROM Products


GROUP BY va HAVING operatorlari
T-SQL da ma`lumotlarni guruhlash uchun GROUP BY va HAVING operatorlari quyidagi rasmiy sintaksis bilan ishlatiladi:
SELECT ustunlar
FROM jadval
[WHERE filtr shartlari]
[GROUP BY guruhlash uchun ustunlar]
[HAVING guruhlarni filtrlash uchun shart]
[ORDER BY tartiblash uchun ustunlar]


GROUP BY
GROUP BY opertatori qatorlarning qanday guruhlanishini belgilaydi.
Masalan, mahsulotlarni ishlab chiqaruvchilar bo'yicha guruhlarga ajratamiz:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer

SELECT ifodasidagi birinchi ustun, Manufacturer, guruh nomini, ikkinchi ustun ModelsCount esa, guruhdagi qatorlar sonini hisoblaydigan Count funksiyasi natijasini aks ettiradi.

Shuni yodda tutish kerakki, SELECT ifodasida ishlatiladigan har qanday ustun (agregat funksiyalarining natijasini saqlaydigan ustunlardan tashqari) GROUP BY operatoridan keyin ko`rsatilishi kerak. Masalan, yuqoridagi holatda, Manufacturer ustuni ham SELECT ifodasida ham, GROUP BY bandida ham ko`rsatilgan.
Agar SELECT ifodasi bir yoki bir nechta ustunlarni tanlasa, hamda agregat funksiyalaridan foydalansa, unda GROUP BY ifodasini ishlatish kerak. Masalan quyidagi misol ishlamaydi, chunki unda guruhlash ifodasi mavjud emas:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
Yana bir misol, mahsulot soni bo'yicha guruhlash qo’shilgan holat:
SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer, ProductCount

GROUP BY operatori bir nechta ustunlar bo’yicha guruhlashi mumkin.

Agar guruhlashtirilayotgan ustunda NULL qiymati bo'lsa, u holda NULL qiymatiga ega qatorlar alohida guruh hosil qiladi.
GROUP BY bandi WHERE bandidan keyin, lekin ORDER BY bandidan oldin kelishi kerak.
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
WHERE Price > 30000
GROUP BY Manufacturer
ORDER BY ModelsCount DESC