|
Mundarija kirish mavzuBog'liq Mundarija kirishSELECT
EMPL_NUM,
NAME,
SUM(AMOUNT)
FROM
ORDERS, SALESREPS WHERE REP = EMPL_NUM GROUP BY
EMPL_NUM, NAME
Yana soddaroq shakl: Xar bir xizmatchi uchun buyurtmalar umumiy
sonini hisoblash.
SELECT NAME, SUM(AMOUNT) FROM ORDERS, SALESREPS
WHERE REP = EMPL_NUM GROUP BY NAME
Agar guruxlash maydonlaridan birida NULL qiymat mavjud bo„lsa
qaysi guruxga tegishli bo„ladi? 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.
Guruxlash va HAVING yordamida ajratish:
Shart bo„yicha satrlarni ajratish uchun WHERE ifodasidan
foydalangan edik. Shart bo„yicha guruxlarni ajratish uchun HAVING
operatori mavjuddir. Uning sintaksisi WHERE operatori bilan bir xil va
ulardan birgalikda foydalanigsh mumkin. Quyidagi so„rovni ko„ramiz:
Buyurtmalar umumiy narxi $300 dan ortiq xizmatchilar uchun buyurtma
o„rtacha narxi qanchaga teng?
SELECT REP, AVG(AMOUNT) FROM ORDERS GROUP BY
REP HAVING SUM(AMOUNT) > 300
Ko„rinib turibdiki HAVING SUM(AMOUNT) > 300 ifodasi
satrlarni guruxlash Sharti sifatida kelmoqda. Agar SUM(AMOUNT) >
300 Sharti yolg„on bo„lsa, bu gurux natijaviy to„plamdan chiqariladi.
Agar rost bo„lsa gurux natijaviy to„plamga kiradi! Yana bir misol
ko„raylik: Ikki va undan ortiq xizmatchiga ega xar bir ofisning xamma
xizmatchilari uchun rejadagi va xaqiqiy sotuvlar umumiy xajmini
xisoblash.
85
SELECT
CITY,
SUM(QUOTA),
SUM(SALESREPS.SALES)
FROM
OFFICES,
SALESREPS
WHERE
OFFICE
|
| |
