129. O'rtacha: NULL larni hisobga olish
O'rtachaAVG NULL qiymatlarni maxrajga qo'shmaydi. Ba'zan esa bizga
"baho qo'yilmagan = 0 ball" mantig'i kerak bo'ladi. Ikkalasini yonma-yon
hisoblang:
avg_skipping_nulls— oddiyAVG(score), ya'ni faqat baho qo'yilganlar
bo'yicha
avg_nulls_as_zero—NULLni0deb hisoblagandagi o'rtacha
(maxraj — barcha talabalar)
graded— bahosi bor talabalar sonitotal— barcha talabalar soni
Ikkala o'rtacha ham 2 xonagacha yaxlitlansin.
Ustunlar: avg_skipping_nulls, avg_nulls_as_zero, graded, total.
students: id INT PK, name VARCHAR, score INT NULL
Namuna ma'lumotlar
sample
students
| idINT | nameVARCHAR(60) | scoreINT |
|---|---|---|
| 1 | Aziz Karimov | 80 |
| 2 | Malika Yusupova | NULL |
| 3 | Bekzod Rahimov | 90 |
Kutilgan natija
| total | graded | avg_nulls_as_zero | avg_skipping_nulls |
|---|---|---|---|
| 3 | 2 | 56.67 | 85 |
sample-full
students
| idINT | nameVARCHAR(60) | scoreINT |
|---|---|---|
| 1 | Aziz Karimov | 80 |
| 2 | Malika Yusupova | NULL |
| 3 | Bekzod Rahimov | 90 |
| 4 | Nilufar Karimova | 70 |
| 5 | Jasur Abdullayev | NULL |
| 6 | Dilnoza Toshpulatova | 100 |
| 7 | Sardor Rahimov | 60 |
| 8 | Zilola Ergasheva | NULL |
| 9 | Otabek Nazarov | 85 |
| 10 | Kamola Abdullayeva | 75 |
| 11 | Shahzod Yusupov | NULL |
| 12 | Gulnora Nazarova | 95 |
| 13 | Farrux Karimov | 65 |
| 14 | Aziza Sobirova | NULL |
Kutilgan natija
| total | graded | avg_nulls_as_zero | avg_skipping_nulls |
|---|---|---|---|
| 14 | 9 | 51.43 | 80 |
sample-extra
students
| idINT | nameVARCHAR(60) | scoreINT |
|---|---|---|
| 1 | Farrux Boboyev | 63 |
| 2 | Davron Usmonov | 81 |
| 4 | Anvar Valiyev | 70 |
| 5 | Kamola Qodirova | 69 |
Kutilgan natija
| total | graded | avg_nulls_as_zero | avg_skipping_nulls |
|---|---|---|---|
| 4 | 4 | 70.75 | 70.75 |
O'xshash masalalar
Konsol
Natijani ko'rish uchun so'rovni ishga tushiring.