139. Sotuv: GROUPING SETS
QiyinBitta so'rovda uchta har xil kesimni birdan chiqaring:
- viloyat bo'yicha jami
- kategoriya bo'yicha jami
- umumiy jami (barchasi bo'yicha bitta qator)
Buning uchun GROUP BY GROUPING SETS ((region), (category), ()) ishlatiladi.
Guruhlashda qatnashmagan ustun o'sha qatorda NULL bo'lib chiqadi — masalan
viloyat kesimida category NULL.
Ustunlar: region, category, revenue.
Saralash: region alifbo (NULL lar oxirida), keyin category
alifbo (NULL lar oxirida).
sales: id INT PK, region VARCHAR, category VARCHAR, amount NUMERIC(12,2)
Jadval: sales
| Ustun | Turi |
|---|---|
| id | INT PK |
| region | VARCHAR(30) |
| category | VARCHAR(30) |
| amount | NUMERIC(12,2) |
Namuna ma'lumotlar
sample
sales
| idINT | regionVARCHAR(30) | categoryVARCHAR(30) | amountNUMERIC(12,2) |
|---|---|---|---|
| 1 | Toshkent | Elektronika | 900000 |
| 2 | Toshkent | Kiyim | 400000 |
| 5 | Samarqand | Kitob | 120000 |
Kutilgan natija
| region | revenue | category |
|---|---|---|
| Samarqand | 120000 | NULL |
| Toshkent | 1300000 | NULL |
| NULL | 900000 | Elektronika |
| NULL | 120000 | Kitob |
| NULL | 400000 | Kiyim |
| NULL | 1420000 | NULL |
sample-full
sales
| idINT | regionVARCHAR(30) | categoryVARCHAR(30) | amountNUMERIC(12,2) |
|---|---|---|---|
| 1 | Toshkent | Elektronika | 900000 |
| 2 | Toshkent | Kiyim | 400000 |
| 3 | Toshkent | Elektronika | 750000 |
| 4 | Samarqand | Kiyim | 600000 |
| 5 | Samarqand | Kitob | 120000 |
| 6 | Samarqand | Elektronika | 480000 |
| 7 | Buxoro | Kitob | 95000 |
| 8 | Buxoro | Kiyim | 300000 |
| 9 | Buxoro | Elektronika | 1200000 |
| 10 | Andijon | Kitob | 60000 |
| 11 | Andijon | Kiyim | 250000 |
| 12 | Andijon | Elektronika | 560000 |
| 13 | Toshkent | Kitob | 990000 |
| 14 | Samarqand | Kiyim | 800000 |
Kutilgan natija
| region | revenue | category |
|---|---|---|
| Andijon | 870000 | NULL |
| Buxoro | 1595000 | NULL |
| Samarqand | 2000000 | NULL |
| Toshkent | 3040000 | NULL |
| NULL | 3890000 | Elektronika |
| NULL | 1265000 | Kitob |
| NULL | 2350000 | Kiyim |
| NULL | 7505000 | NULL |
sample-extra
sales
| idINT | regionVARCHAR(30) | categoryVARCHAR(30) | amountNUMERIC(12,2) |
|---|---|---|---|
| 1 | Toshkent | Elektronika | 176600 |
| 2 | Samarqand | Elektronika | 43927.56 |
| 3 | Toshkent | Elektronika | 43927.56 |
| 4 | Toshkent | Kitob | 43927.56 |
| 5 | Toshkent | Elektronika | 43927.56 |
| 6 | Andijon | Elektronika | 176600 |
| 7 | Toshkent | Kitob | 43927.56 |
| 8 | Andijon | Elektronika | 176600 |
| 9 | Buxoro | Kitob | 43927.56 |
| 10 | Andijon | Elektronika | 43927.56 |
Kutilgan natija
| region | revenue | category |
|---|---|---|
| Andijon | 397127.56 | NULL |
| Buxoro | 43927.56 | NULL |
| Samarqand | 43927.56 | NULL |
| Toshkent | 352310.24 | NULL |
| NULL | 705510.24 | Elektronika |
| NULL | 131782.68 | Kitob |
| NULL | 837292.92 | NULL |
O'xshash masalalar
Konsol
Natijani ko'rish uchun so'rovni ishga tushiring.