MySQL GROUP BY
MySQL GROUP BY operatori
GROUP BY iborasi bir xil qiymatlarga ega bo‘lgan qatorlarni “Har bir mamlakatdagi mijozlar sonini toping” kabi umumiy qatorlarga guruhlash uchun ishlatiladi.
GROUP BY iborasi deyarli har doim har bir guruh bo‘yicha hisob-kitoblarni bajarish uchun COUNT(), MAX(), MIN(), SUM(), AVG() kabi jamlama funksiyalar bilan birgalikda ishlatiladi.
Sintaksis bo‘yicha GURUHLASH
SELECT column1, aggregate_function(column2), column3, ...
FROM table_name
WHERE condition
GROUP BY column1, column3
ORDER BY column_name;
Demo ma’lumotlar bazasi
Quyida Northwind namunasidagi "Mijozlar" jadvalidan tanlov mavjud ma’lumotlar bazasi:
| Mijoz ID | Mijoz nomi | Kontakt nomi | Manzil | Shahar | Pochta kodi | Mamlakat |
|---|---|---|---|---|---|---|
| 1 |
Alfreds Futterkiste | Mariya Anders | Obere ko‘chasi. 57 | Berlin | 12209 | Germaniya |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Konstitutsiya 2222 | Meksika D.F. | 05021 | Meksika |
| 3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Meksika D.F. | 05023 | Meksika |
| 4 |
Shox atrofida | Tomas Xardi | 120 Gannover kv. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Kristina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Shvetsiya |
MySQL GROUP BY. Misollar
Quyidagi SQL har bir mamlakatdagi mijozlar sonini qaytaradi:
Misol
SELECT Country, COUNT(CustomerID) AS "Number of Customers"
FROM Customers
GROUP BY Country;
O‘zingiz sinab ko‘ring »
Quyidagi SQL har bir mamlakatdagi mijozlar sonini yuqoridan pastgacha saralab beradi:
Misol
SELECT Country, COUNT(CustomerID) AS "Number of Customers"
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
O‘zingiz sinab ko‘ring »
Demo ma’lumotlar bazasi
Quyida Northwind namunaviy ma’lumotlar bazasidagi "Orders" jadvalidan tanlov keltirilgan:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 1996-07-04 | 3 |
| 10249 | 81 | 6 | 1996-07-05 | 1 |
| 10250 | 34 | 4 | 1996-07-08 | 2 |
Va "Yuk yuboruvchilar" jadvalidan tanlov:
| ShipperID | ShipperName |
|---|---|
| 1 | Speedy Express |
| 2 | United Package |
| 3 | Federal Shipping |
JOIN bilan GROUP BY Misol
Quyidagi SQL operatorida har bir jo‘natuvchi tomonidan yuborilgan buyurtmalar soni ko‘rsatilgan:
Misol
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS "Number of Orders"
FROM
Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
O‘zingiz sinab ko‘ring »
W3Schools Pathfinder
Yutuqlaringizni kuzating – bu bepul!