SELECT NAM E, SUM (AM OLNT) FROM ORDERS, SALESREPS WHERE REP - EM PLJNUM GROUP BY NAMEGuruhlash va HAVING yordamida ajratishSELECT REP, AVG(AM OUNT) FROM ORDERS GROUP BY REP HAVING SUM(AMOUNT) > 300SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES) FROM OFFICES, SALESREPS W HERE OFFICE = REP OFFICE GROUPDESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY) FROM PRODUCTS, ORDERS W HERE M FR = M F R J D GROUPBY QTY_ON_HAND DESC |
Zbek1ston respublikasi axborot
|
| bet | 51/71 | | Sana | 28.05.2024 | | Hajmi | 24,2 Mb. | | #255852 |
Bog'liq Ma\'lumotlar bazasi. ZaynidinovX.N.Bu sahifa navigatsiya:
- SELECT NAM E, SUM (AM OLNT) FROM ORDERS, SALESREPS WHERE REP - EM PLJNUM GROUP BY NAME
- Guruhlash va HAVING yordamida ajratish
- SELECT REP, AVG(AM OUNT) FROM ORDERS GROUP BY REP HAVING SUM(AMOUNT) > 300
- SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES) FROM OFFICES, SALESREPS W HERE OFFICE = REP OFFICE GROUP
- DESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY) FROM PRODUCTS, ORDERS W HERE M FR = M F R J D GROUP
- BY QTY_ON_HAND DESC
SELECT EM PLJ 4 U M , NAM E, SUM(AMOUNT) FROM ORDERS, SALESREPS W HERE REP = EM PL NUM GROUP BY EM PL NUM, NAME
86
Yana soddaroq shakl: Har bir xizmatchi uchun buyurtmalar umumiy sonini hisoblash.
SELECT NAM E, SUM (AM OLNT) FROM ORDERS, SALESREPS WHERE REP - EM PLJNUM GROUP BY NAME
Agar guruhlash inaydonlaridan birida NULL qiyinat mavjud b o isa qaysi guruhga tegishli boiadi? WHERE ifodasida NULL va NULL tenglikka solishtirish natijasi Yana NULL beradi. SHuning uchun ANSI/ISO standartida GROUP BY ifodasida NULL qiymatlar teng deb qabul qilingan.
Guruhlash va HAVING yordamida ajratish:
Shart bo‘yicha satrlami ajratish uchun W HERE ifodasidan foydalangan edik. Shart bo'yicha guruhlami ajratish uchun HAVING operatori mavjuddir. Uning sintaksisi WHERE operatori bilan bir xii va ulardan birgalikda foydalanigsh mumkin. Quyidagi so‘rovm koTamiz: Buyurtmalar umumiy narxi $300 dan ortiq xizmatchi las uchun buyurtma o'rtacha narxi qanchaga teng?
SELECT REP, AVG(AM OUNT) FROM ORDERS GROUP BY
REP HAVING SUM(AMOUNT) > 300
K o‘rinib turibdiki HAVING SUM(AMOUNT) > 300 ifodasi satrlami guruhlash sharti sifatida kelmoqda. Agar SUM(AMOUNT) >
300 Sharti yolg‘on bo'lsa. bu guruh natijaviy to‘plamdan chiqariladi. Agar rost b o isa guruh natijaviy to ‘plamga kiradi! Yana bir misol k o ’niylik. Ikki va undan ortiq xizmatchiga ega har bir ofisning hamma xizmatchilari uchun rejadagi va haqiqiy sotuvlar umumiy hajmini hisoblash.
SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES) FROM OFFICES, SALESREPS W HERE OFFICE = REP OFFICE GROUP BY CITY HAVING COUNT(*) >= 2
Bu misolda WHERE va HAVING ifodalari o ‘z funksiyalarini bajaradilar. Shunga e ’tibor berish kerakki HAVING ifodasida agregat funksiyalardan foydalaniladi.
Q o‘shilgan jadval satrlarlari ofislar bo‘yicha gumhlanadilar.
87
Ikkidan kam satrga ega guruhlar tashlab yuboriladi. Ular HAVING ifodasi talabiga javob bevmaydilav. Har bir guruh uchun haqiqiy va rejadagi sotuvlar hajmlari hisoblanadi. Murakkabroq mi so 1m ko‘ramiz: Har bir tovar nomi uchun narxi, ombordagi soni va buyurtma berilganlar umumiy sonini ko‘rsating, agar uning uchun buyiu'tma berilganlar umumiy soni ombordagi umumiy soni 75 foizidan k o ‘p bo‘lsa.
SE L E C T DESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY) FROM PRODUCTS, ORDERS W HERE M FR =
M F R J D GROUP BY M F R JD , PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND HAVING SUM(QTY) > (0.75 * QTY_ON_HAND) ORDER BY QTY_ON_HAND DESC
HAVING uchung qo'sbim cha chegaralar mavjuddir. Bu ifoda juda hoim asa bitta agregat fiinksiyani o ‘z ichiga olishi kerak. Chunki W HERE aiohida satrlarga HAVING satrlar guruhlariga qoMianadi. NULL qiymat uchun WHERF. ifodasiga o'xshab quyidagi qoida e'rinli Agar izlash sharti NULL qiymatga ega bo'lsa satrlar guruhi tashlab yuboriladi. HAVING ifodasini GROUP BYsiz qoTlash mumkin. Bu holda natija hamma satrlardan iborat guruh deb qaraladi, lekin amalda bu kam qoilanadi.
|
| |
