Сложный запрос MySql SELECT + GROUP + SUM [дубликат]

Ну как-то так.

SELECT название,
SUM(IF(вид = 'ампулы', количество, 0)) as ампулы,
SUM(IF(вид = 'таблетки', количество, 0)) as таблетки
FROM table_name
GROUP BY название

Тестовые данные

Будем использовать следущую DDL и данные

CREATE TABLE `supply` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `type` enum('Ампулы','Таблетки') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO supply VALUES
  (default, "Капсавир", 15, "Ампулы"),
  (default, "Капсавир", 35, "Таблетки"),
  (default, "Житовир", 36, "Таблетки"),
  (default, "Житовир", 77, "Ампулы"),
  (default, "Капсавир", 15, "Ампулы"),
  (default, "Капсавир", 35, "Таблетки"),
  (default, "Житовир", 36, "Таблетки"),
  (default, "Житовир", 77, "Ампулы"),
  (default, "Витапрост", 77, "Ампулы"),
  (default, "Ингавирин", 77, "Таблетки")
;

Составление запроса

В концепции РСУБД результат запроса является таблицей. А полученные таблицы-запросы можно соединять. И еще раз соединять. Очень гибкая система. Посмотрим как я мыслил, чтобы решить эту задачу.

Все примеры лучше выполнять в консоли MySQL, так нагляднее (и не будет "схлопывания" столбцов, если совпадают названия).

Простой запрос с агрегацией

Построим два отдельных запроса: один для ампул, другой для таблеток:

SELECT name, SUM(quantity), type FROM supply WHERE type = "Ампулы" GROUP BY name, type;
+--------------------+---------------+--------------+
| name               | SUM(quantity) | type         |
+--------------------+---------------+--------------+
| Витапрост          |            77 | Ампулы       |
| Житовир            |           154 | Ампулы       |
| Капсавир           |            30 | Ампулы       |
+--------------------+---------------+--------------+
SELECT name, SUM(quantity), type FROM supply WHERE type = "Таблетки" GROUP BY name, type;
+--------------------+---------------+------------------+
| name               | SUM(quantity) | type             |
+--------------------+---------------+------------------+
| Житовир            |            72 | Таблетки         |
| Ингавирин          |            77 | Таблетки         |
| Капсавир           |            70 | Таблетки         |
+--------------------+---------------+------------------+

Прекрасно! Теперь эти две таблицы нужно соединить по имени.

Соединяем две таблицы в одну

Просто возьмем два прошлых "простых" запроса и соединим по критерию "имя". Для этого лучше использовать FULL OUTER JOIN, но в MySQL его нет, печалька...

Возьмем LEFT JOIN, чтобы вы поняли, как соединились таблицы (сравните левую и правую части результата):

SELECT * FROM
  (SELECT name, SUM(quantity), type FROM supply WHERE type = "Ампулы" GROUP BY name, type) AS `ampules`
LEFT JOIN
  (SELECT name, SUM(quantity), type FROM supply WHERE type = "Таблетки" GROUP BY name, type) AS `pills` ON (ampules.name = pills.name);
+--------------------+---------------+--------------+------------------+---------------+------------------+
| name               | SUM(quantity) | type         | name             | SUM(quantity) | type             |
+--------------------+---------------+--------------+------------------+---------------+------------------+
| Витапрост          |            77 | Ампулы       | NULL             |          NULL | NULL             |
| Житовир            |           154 | Ампулы       | Житовир          |            72 | Таблетки         |
| Капсавир           |            30 | Ампулы       | Капсавир         |            70 | Таблетки         |
+--------------------+---------------+--------------+------------------+---------------+------------------+

Конструкция AS позволит использовать псевдонимы. Столбец type исключим за ненадобностью. Также проверим, все ли верно:

SELECT * FROM
  (SELECT name, SUM(quantity) AS ampules_count FROM supply WHERE type = "Ампулы" GROUP BY name) AS `ampules`
LEFT JOIN
  (SELECT name, SUM(quantity) AS pills_count FROM supply WHERE type = "Таблетки" GROUP BY name) AS `pills` ON (ampules.name = pills.name);
+--------------------+---------------+------------------+-------------+
| name               | ampules_count | name             | pills_count |
+--------------------+---------------+------------------+-------------+
| Витапрост          |            77 | NULL             |        NULL |
| Житовир            |           154 | Житовир          |          72 |
| Капсавир           |            30 | Капсавир         |          70 |
+--------------------+---------------+------------------+-------------+

Нет препарата "Ингавирин"! LEFT JOIN добавил в правую часть таблицы NULL, там где нет совпадения в левой части, но наоборот не сделал!

Ничего, это поправимо через UNION, но во второй части используется RIGHT JOIN. Таким способом получим эмуляцию FULL OUTER JOIN:

SELECT * FROM
  (SELECT name, SUM(quantity) AS ampules_count FROM supply WHERE type = "Ампулы" GROUP BY name) AS `ampules`
LEFT JOIN
  (SELECT name, SUM(quantity) AS pills_count FROM supply WHERE type = "Таблетки" GROUP BY name) AS `pills` ON (ampules.name = pills.name)
UNION
SELECT * FROM
  (SELECT name, SUM(quantity) AS ampules_count FROM supply WHERE type = "Ампулы" GROUP BY name) AS `ampules`
RIGHT JOIN
  (SELECT name, SUM(quantity) AS pills_count FROM supply WHERE type = "Таблетки" GROUP BY name) AS `pills` ON (ampules.name = pills.name)
  ;
+--------------------+---------------+--------------------+-------------+
| name               | ampules_count | name               | pills_count |
+--------------------+---------------+--------------------+-------------+
| Витапрост          |            77 | NULL               |        NULL |
| Житовир            |           154 | Житовир            |          72 |
| Капсавир           |            30 | Капсавир           |          70 |
| NULL               |          NULL | Ингавирин          |          77 |
+--------------------+---------------+--------------------+-------------+

Отлично, теперь нужно доработать таблицу до ожидаемого результата.

Доработаем таблицу до наших потребностей

Как бы заменить NULL в названии? Будем использовать IFNULL: если первый агрумент NULL, выбирается второй.

SELECT * FROM (
  SELECT IFNULL(ampules.name, pills.name) AS name, ampules_count, pills_count FROM
    (SELECT name, SUM(quantity) AS ampules_count FROM supply WHERE type = "Ампулы" GROUP BY name) AS `ampules`
  LEFT JOIN
    (SELECT name, SUM(quantity) AS pills_count FROM supply WHERE type = "Таблетки" GROUP BY name) AS `pills` ON (ampules.name = pills.name)
  ) AS `alias_1`
UNION
SELECT * FROM (
  SELECT IFNULL(ampules.name, pills.name) AS name, ampules_count, pills_count FROM
    (SELECT name, SUM(quantity) AS ampules_count FROM supply WHERE type = "Ампулы" GROUP BY name) AS `ampules`
  RIGHT JOIN
    (SELECT name, SUM(quantity) AS pills_count FROM supply WHERE type = "Таблетки" GROUP BY name) AS `pills` ON (ampules.name = pills.name)
  ) AS `alias_2`;

+--------------------+---------------+-------------+
| name               | ampules_count | pills_count |
+--------------------+---------------+-------------+
| Витапрост          |            77 |        NULL |
| Житовир            |           154 |          72 |
| Капсавир           |            30 |          70 |
| Ингавирин          |          NULL |          77 |
+--------------------+---------------+-------------+

Сравниваем со способом Arendach

SELECT
  name,
  SUM(IF(`type` = 'Ампулы', quantity, 0)) as ампулы,
  SUM(IF(`type` = 'Таблетки', quantity, 0)) as таблетки
FROM supply
GROUP BY name;
+--------------------+--------------+------------------+
| name               | ампулы       | таблетки         |
+--------------------+--------------+------------------+
| Витапрост          |           77 |                0 |
| Житовир            |          154 |               72 |
| Ингавирин          |            0 |               77 |
| Капсавир           |           30 |               70 |
+--------------------+--------------+------------------+

Запрос короче, результаты - одинаковые. Ну почему я выбрал такой трудный путь...