Как в SQL сгруппировать по 1 параметру, а второй получить в виде массива/строки?

Question

[stdio96]

Здравствуйте.

Есть таблица вида:
Город | Товар | Поставщик
Город | Товар | Поставщик
Город | Товар | Поставщик
Город | Товар | Поставщик

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

SELECT *, COUNT(`city`) AS count FROM `ctable` GROUP BY `city`, `vendor`

Запрос выдает список городов - количество товара - поставщика (в таком случае я получаю 1 и тот же город несколько раз из-за того, что поставщиков у товара может быть несколько), но мне надо, чтобы запрос суммировал количество товара в городе, а поставщиков указывал через запятую (или другой знак, не важно).

Как сейчас:

Москва - 25 - vendor1
Москва - 15 - vendor2
Москва - 10 - vendor3

Как надо:

Москва - 50 - vendor1, vendor2, vendor3

P.S.: не ищите смысл в задании. Интересует, как сделать такой запрос.

Спасибо.

Answer 1

[Алексей]

SELECT city, COUNT(city) AS count, string_agg(vendor, ',') FROM ctable GROUP BY city

https://docs.microsoft.com/en-us/sql/t-sql/functio...
https://www.postgresql.org/docs/9.5/functions-aggr...

Возможно нужно использовать GROUP_CONCAT, зависит от БД

SELECT city, COUNT(city) AS count, GROUP_CONCAT(vendor) FROM ctable GROUP BY city

www.sqlines.com/mysql/functions/group_concat

Comments

[stdio96]

Да, уже выводит почти так, как надо. Но оно выводит список поставщиков, в котором они встречаются по нескольку раз:

Москва - 50 - vendor1, vendor1, vendor1, vendor1, vendor2, vendor2, vendor2, vendor3, vendor3

[stdio96]

Я так полагаю, что надо использовать DISTINCT где-то в запросе

[stdio96]

Да, разобрался) Большое спасибо!

[Алексей]

Все верно, distinct vendor

[stdio96]

Алексей, еще такое небольшое дополнение к вопросу. Как вывести количество производителей? К примеру:

Москва - 50 - vendor1 (25), vendor2 (15), vendor3 (10)

[Алексей]

Потребуется отдельный подзапрос с группировкой по вендору, джоин по city и группировка. Подзапрос примерно такого вида, в результате должна сформироваться строка вида vendor1 (15) для каждого вендора, которую потом вы сгруппируете

SELECT city, CONCAT(vendor, COUNT(*))) as vendor_and_count FROM ctable GROUP BY city, vendor

[stdio96]

Надо к уже существующему запросу просто добавить JOIN? Вы это имеете ввиду?

[Алексей]

сейчас попробую полностью написать

[Алексей]

SELECT city, any(count) as count, GROUP_CONCAT(vendor_and_count) FROM (
     SELECT city, COUNT(city) AS count FROM ctable GROUP BY city
) LEFT JOIN (
     SELECT city, CONCAT(vendor, " (", COUNT(*), ")")) as vendor_and_count FROM ctable GROUP BY city, vendor
) USING city
GROUP BY city

Примерно так, либо возможно через подзапросы

SELECT city, (SELECT count(*) from ctable c WHERE c.city = city) as count, CONCAT(vendor, " (", COUNT(*), ")")) as vendor_and_count FROM ctable GROUP BY city, vendor

По синтаксису где то могу опечататься, т.к. не могу проверить

[stdio96]

Второй запрос не работает: просто выводит количество всех товаров во всех городах, а вторая часть (vendor_and_count) выводит просто количество товаров в городе.

Первый запрос еще не пробовал.

Но идею я понял, буду пробовать что-то изменить.

Answer 2

[Rsa97]

GROUP_CONCAT()

Answer 3

[Dmitry Bay]

SELECT *, COUNT(`city`) AS count, sum('price'), GROUP_CONCAT(`vendor`) FROM `ctable` GROUP BY `city`, `vendor`

webi.ru/webi_articles/8_14_f.html

Answer 4

[Константин Цветков]

Aggregate (GROUP BY) Function.