SQL GROUP BY bandi
SQL GROUP BY buyrug‘i
GROUP BY buyrug‘i bir xil qiymatlarga ega qatorlarni umumlashtiruvchi qatorlarga guruhlash uchun ishlatiladi, masalan, "Har bir mamlakatdagi mijozlar sonini toping".
GROUP BY buyrug‘i har bir guruh bo‘yicha hisob-kitoblarni bajarish uchun deyarli har doim COUNT(), MAX(), MIN(), SUM(), AVG() kabi agregat funksiyalar bilan birgalikda ishlatiladi.
GROUP BY sintaksisi
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 namunaviy ma’lumotlar bazasidagi "Customers" jadvalidan tanlanma keltirilgan:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 |
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SQL GROUP BY misollari
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 kattadan kichikka qarab saralangan holda qaytaradi:
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 tanlanma 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 tanlanma "Shippers" jadvali:
| ShipperID | ShipperName |
|---|---|
| 1 | Speedy Express |
| 2 | United Package |
| 3 | Federal Shipping |
JOIN bilan GROUP BY misoli
Quyidagi SQL har bir jo‘natuvchi tomonidan yuborilgan Orders sonini qaytaradi:
Misol
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM
Orders
LEFT JOIN Shippers
ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
O‘zingiz sinab ko‘ring »
W3Schools Pathfinder
Yutuqlaringizni kuzating – bu bepul!