GROUP BY: отчет по групи

Накратко: GROUP BY събира редовете с еднаква стойност в група, за да сметне брой, сума или средна за всяка група поотделно.

Проблем

Таблото на склада

Складът иска табло: колко поръчки са нови, колко платени, колко изпратени. С COUNT(*) и WHERE status = 'new' ще ти трябват пет заявки, по една за всяко състояние. А ако утре се появи шесто състояние, отчетът няма да го покаже.

Трябва една заявка, която сама намира състоянията и брои всяко.

Идеята

GROUP BY

GROUP BY колона слага в една група редовете с еднаква стойност в колоната. Обобщаващата функция се смята за всяка група поотделно:

SQLбаза: магазин

SELECT status, COUNT(*)
FROM orders
GROUP BY status;

SELECT status, COUNT(*) FROM orders GROUP BY status;

▶ ИзпълниВърни заявкатаНова база

60 поръчки, пет групи, пет реда. Всяка група дава един ред.

Клиентите по град:

SQLбаза: магазин

SELECT city, COUNT(*)
FROM customers
GROUP BY city;

SELECT city, COUNT(*) FROM customers GROUP BY city;

▶ ИзпълниВърни заявкатаНова база

Борис е без град и е в отделна група с city = NULL. За GROUP BY, както и за DISTINCT, всички NULL-и са една група.

Как една група става един ред, се вижда на малко редове. Последните три поръчки са шест реда в order_items:

SQLбаза: магазин

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE order_id >= 58;

SELECT order_id, product_id, quantity, unit_price FROM order_items WHERE order_id >= 58;

▶ ИзпълниВърни заявкатаНова база

SELECT order_id, COUNT(*) AS lines, ROUND(SUM(quantity * unit_price), 2) AS total
FROM order_items
WHERE order_id >= 58
GROUP BY order_id;

Стъпка по стъпка, с думи

1. 6 реда от order_items: продуктите в поръчки 58, 59 и 60.
2. GROUP BY order_id: всеки ред отива в групата на своя order_id.
3. 3 различни order_id, 3 групи. Във всяка има по 2 реда.
4. Групата на 58 става един ред: COUNT(*) е 2, а SUM(quantity * unit_price) е 1 × 1,20 + 4 × 1,90 = 8,80 €.
5. Групата на 59: 2 реда, 3 × 2,50 + 2 × 3,20 = 13,90 €.
6. Групата на 60: 2 реда, 2 × 1,20 + 4 × 2,90 = 14,00 €.
7. 6 реда влязоха, 3 излязоха: по един за всяка група.

Заявката от сцената. ROUND(…, 2) закръгля сбора до цент:

SQLбаза: магазин

SELECT order_id, COUNT(*) AS lines, ROUND(SUM(quantity * unit_price), 2) AS total
FROM order_items
WHERE order_id >= 58
GROUP BY order_id;

SELECT order_id, COUNT(*) AS lines, ROUND(SUM(quantity * unit_price), 2) AS total FROM order_items WHERE order_id >= 58 GROUP BY order_id;

▶ ИзпълниВърни заявкатаНова база

Пробвай

Поръчки на клиент

Колко поръчки е направил всеки клиент? Покажи customer_id и броя.

SQLбаза: магазин

SELECT customer_id FROM orders;

SELECT customer_id FROM orders;

Провери▶ Изпълни

Идеята

Стойността на всяка поръчка

В order_items поръчката е на няколко реда, по един за всеки продукт. Групата по order_id събира редовете на една поръчка:

SQLбаза: магазин

SELECT order_id, COUNT(*) AS lines, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;

SELECT order_id, COUNT(*) AS lines, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id;

▶ ИзпълниВърни заявкатаНова база

Сега всяка поръчка е един ред: колко продукта има и за колко пари е.

Групата може да е и по две колони. Тогава в една група са редовете, в които и двете стойности са еднакви. Поръчките на клиент 1 по състояние:

SQLбаза: магазин

SELECT customer_id, status, COUNT(*)
FROM orders
WHERE customer_id = 1
GROUP BY customer_id, status;

SELECT customer_id, status, COUNT(*) FROM orders WHERE customer_id = 1 GROUP BY customer_id, status;

▶ ИзпълниВърни заявкатаНова база

Групата може да е и по изчислена стойност. strftime('%Y-%m', ordered_at) взима годината и месеца от датата, и поръчките се групират по месец:

SQLбаза: магазин

SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*)
FROM orders
GROUP BY strftime('%Y-%m', ordered_at);

SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*) FROM orders GROUP BY strftime('%Y-%m', ordered_at);

▶ ИзпълниВърни заявкатаНова база

Виж го

Колоната, която не е в групата

Поръчките по клиент, а до тях и състоянието. Изглежда разумно:

SQLбаза: магазин

SELECT customer_id, status, COUNT(*)
FROM orders
GROUP BY customer_id;

SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id;

▶ ИзпълниВърни заявкатаНова база

1. Заявката показа „8 | cancelled | 3“. Какво значи това?

Клиент 8 е отказал три пътиКлиент 8 има три поръчки, а cancelled е състоянието на една от тях, избрана от SQLiteКлиент 8 има три отказани поръчки

Провери

Поръчките на клиент 8 показват какво става:

SQLбаза: магазин

SELECT id, status FROM orders WHERE customer_id = 8;

SELECT id, status FROM orders WHERE customer_id = 8;

▶ ИзпълниВърни заявкатаНова база

Три поръчки, три различни състояния. Групата е един ред, а в него има място за една стойност. SQLite не казва нищо и взима състоянието от един от редовете. Кой, не е обещано.

Правилото: всяка колона в SELECT е или в GROUP BY, или в обобщение (COUNT, SUM, MIN…). PostgreSQL и SQL Server дават грешка за такава заявка, MySQL също, с настройките си по подразбиране от версия 5.7. SQLite я приема и показва нещо, което изглежда като отговор.

Ако искаш състоянията, групирай по двете колони: GROUP BY customer_id, status.

По-точно, за любопитните

SQLite има една записана в документацията особеност: ако в заявката има точно едно MIN или MAX, свободната колона идва от реда с най-малката или най-голямата стойност. SELECT customer_id, MAX(ordered_at), id FROM orders GROUP BY customer_id дава номера на последната поръчка. Това е само в SQLite. Другите бази го отказват, а в модула за подзаявките ще видим как се прави навсякъде.

Пробвай

Най-продаваните

Кои пет продукта са продадени в най-много бройки? Покажи product_id и бройките в колона sold, най-продаваният пръв.

SQLбаза: магазин

Провери▶ Изпълни

Упражнение

Две справки

1. Колко продукта и колко бройки на склад има във всяка категория?

Покажи category_id, броя на продуктите и сбора на наличността им.

SQLбаза: магазин

Провери▶ Изпълни

2. Счетоводството иска броя на поръчките за всеки месец от 2026 г., подредени по месец.

Покажи месеца (година-месец) и броя на поръчките за всеки месец от 2026 г., по ред на месеците.

SQLбаза: магазин

Провери▶ Изпълни

Тест

Провери се

1. Колко реда връща SELECT status, COUNT(*) FROM orders GROUP BY status?

ЕдинТолкова, колкото различни състояния има60, по един за всяка поръчка

2. Борис е без град. Къде е той в SELECT city, COUNT(*) FROM customers GROUP BY city?

Не е в резултатаВ отделна група с city = NULLВ групата на първия град

3. Какво дава GROUP BY customer_id, status?

Група за всеки клиентГрупа за всяка двойка клиент и състояние, която я има в даннитеГрупа за всяко състояние

4. Кое е безопасно в SELECT на заявка с GROUP BY order_id?

Всяка колона от order_itemsСамо обобщенияorder_id и обобщения като SUM(quantity)

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: ORDER BY, LIMIT и DISTINCT · Сметки в заявката · Функции за дати · Агрегатни функции: COUNT, SUM, AVG
• Продължава в: HAVING: условие за групите · Редът на изпълнение на заявката · LEFT JOIN: и редовете без двойка · Много таблици и таблица със себе си
• Задачи по темата: Обобщаване