C, PHP, VB, .NET

Когато имаме повтарящи се данни в дадена колона, то можем да ги „групираме“. За целта се използва фразата „GROUP BY“. Почти винаги този оператор се използва в комбинация с агрегатни функции. Следващите примери ще използват базата данни за банки от миналата статия.

1. Да се изведе сумата на акаунти от тип 1 и сумата на акаунти от тип 2:

mysql> SELECT type, SUM(amount)
FROM accounts
GROUP BY type;
+------+-------------+
| type | SUM(amount) |
+------+-------------+
|    1 |    49468.67 |
|    2 |     8419.00 |
+------+-------------+
2 rows in set (0.00 sec)

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

Ако не присъства агрегатна (обединяваща) функция на останалите колони, то от тях ще бъдат „отрязани“ резултатите на групираните таблици.

2. Изкарайте името на клиентите и сумата от техните сметки в системата:

SELECT customers.name, SUM(accounts.amount)
FROM customers LEFT JOIN accounts ON customers.id = accounts.customer_id
GROUP BY customers.name;
+-------------------+----------------------+
| name              | SUM(accounts.amount) |
+-------------------+----------------------+
| Alain Delrick     |              3910.50 |
| Alexander Smith   |              1536.91 |
| Atanas Petrov     |              1200.00 |
| Devry Henry       |               231.37 |
| Elica Zaharieva   |               211.98 |
| Fontaine Rupert   |               500.00 |
| George Harison    |               991.63 |
| George Lucas      |             19036.07 |
| Ivailo Ivanov     |                99.18 |
| Ivan Ivanov       |               133.48 |
| Lenard Renne      |              9463.23 |
| Maria Smith       |             14921.43 |
| Mariana Zaharieva |              1236.33 |
| Michael Jackson   |               559.32 |
| Mihail Ivchev     |              1331.50 |
| Neno Nenov        |                42.98 |
| Petko Stoianov    |               136.22 |
| Todor Ivanov      |               156.38 |
| Todor Shtilianov  |               317.79 |
| Tony Martin       |              1212.70 |
| Tony McCarter     |               402.26 |
| Zlatomir Petrov   |               256.41 |
+-------------------+----------------------+
22 rows in set (0.00 sec)

В последния пример групирането е по име. Трябва да отбележим, че това попринцип е грешно, защото е възможно да има двама клиенти с едно и също име. Затова е по-добре да групираме по уникалния ключ:

SELECT customers.name, SUM(accounts.amount)
FROM customers LEFT JOIN accounts ON customers.id = accounts.customer_id
GROUP BY customers.id
ORDER BY customers.name;

За да видите разликата, въведете още един клиент с име „Ivan Ivanov“ и направете горните заявки още веднъж. Тук трябва да забележим, че ANSI стандарта изисква задължително в условието GROUP BY да присъства някоя от колоните изредени след SELECT. В MySQL това не е задължително.

3. Изведете имената на банките и средната сума на акаунтите в тях:

mysql> SELECT banks.name, AVG(amount)
FROM banks LEFT JOIN accounts
	ON accounts.branch_id IN(
		SELECT id FROM branches WHERE bank_code = banks.code
	)
GROUP BY banks.name;
+-----------------+-------------+
| name            | AVG(amount) |
+-----------------+-------------+
| Bank of America | 8229.170000 |
| Bulbank         |  426.854167 |
| Societe General | 2821.020000 |
| Wells Fargo     | 2775.247500 |
+-----------------+-------------+
4 rows in set (0.00 sec)

4. За да демонстрираме по-сложни заявки, нека усложним предишния пример малко – изведете името на банката и средната сума на акаунтите в нея, но само за банките, които имат буквата „M“ някъде в името на банковия клон:

mysql> SELECT banks.name, AVG(amount)
FROM banks LEFT JOIN accounts
	ON accounts.branch_id IN(
		SELECT id FROM branches WHERE bank_code = banks.code
	)
WHERE banks.code IN(
	SELECT bank_code
	FROM branches
	WHERE name LIKE '%M%'
)
GROUP BY banks.name;
+-----------------+-------------+
| name            | AVG(amount) |
+-----------------+-------------+
| Societe General | 2821.020000 |
| Wells Fargo     | 2775.247500 |
+-----------------+-------------+
2 rows in set (0.00 sec)

5. Изведете името на банката, клона, името на служителят и общата сума в акаунтите, на чийто клиенти той е мениджър. Получения резултат да се сортира в реда име на банка, име на клон и име на служител:

SELECT banks.name, branches.name, employees.name, SUM(amount)
FROM banks JOIN branches ON banks.code = branches.bank_code
JOIN employees ON branches.id = employees.branch_id
JOIN customers ON employees.id = customers.bank_mgr
JOIN accounts ON customers.id = accounts.customer_id
GROUP BY employees.id
ORDER BY banks.name, branches.name, employees.name;
+-----------------+------------+-----------------+-------------+
| name            | name       | name            | SUM(amount) |
+-----------------+------------+-----------------+-------------+
| Bank of America | LA         | James Pitt      |    16458.34 |
| Bulbank         | Serdica    | Ivan Ivanov     |      292.60 |
| Bulbank         | Serdica    | Ivan Stoianov   |       42.98 |
| Bulbank         | Serdica    | Mihail Zahariev |     1448.31 |
| Bulbank         | Slatina    | Ilian Stoianov  |      317.79 |
| Bulbank         | Slatina    | Milen Stoilov   |     1589.89 |
| Bulbank         | Slatina    | Petar Petrov    |       99.18 |
| Bulbank         | Slatina    | Svilen Petrov   |     1331.50 |
| Societe General | Marseilles | Alfonso Levi    |      500.00 |
| Societe General | Paris      | Francoa Dupres  |    13605.10 |
| Wells Fargo     | Manhattan  | Jimmy Carter    |    21799.72 |
| Wells Fargo     | Manhattan  | Mary Jane       |      402.26 |
+-----------------+------------+-----------------+-------------+
12 rows in set (0.00 sec)

Както виждате GROUP BY, в комбинация с агрегатни функции, ни дава добри възможности за обобщаване на данни и извършване на прости статистически изчисления.

6. Изведете името на банката, клона, името на служителят и общата сума в акаунтите, на чийто клиенти той е мениджър. Получения резултат да се сортира в реда име на банка, име на клон и име на служител, но само за служителите, чието име започва с „M“:

SELECT banks.name, branches.name, employees.name, SUM(amount)
FROM banks JOIN branches ON banks.code = branches.bank_code
	JOIN employees ON branches.id = employees.branch_id
	JOIN customers ON employees.id = customers.bank_mgr
	JOIN accounts ON customers.id = accounts.customer_id
WHERE employees.name LIKE 'M%'
GROUP BY employees.id
ORDER BY banks.name, branches.name, employees.name;
+-------------+-----------+-----------------+-------------+
| name        | name      | name            | SUM(amount) |
+-------------+-----------+-----------------+-------------+
| Bulbank     | Serdica   | Mihail Zahariev |     1448.31 |
| Bulbank     | Slatina   | Milen Stoilov   |     1589.89 |
| Wells Fargo | Manhattan | Mary Jane       |      402.26 |
+-------------+-----------+-----------------+-------------+
3 rows in set (0.00 sec)