MySQL подсчитывает столбцы по определенному значению

У меня есть следующая таблица db, и я хотел бы иметь возможность подсчитать экземпляр продаж определенных продуктов для каждого продавца.

|------------|------------|------------|
|id          |user_id     |product_id  |
|------------|------------|------------|
|1           |1           |2           |
|2           |1           |4           |
|3           |1           |2           |
|4           |2           |1           |
|------------|------------|------------|

Я хотел бы создать набор результатов следующим образом:

|------------|-------------|------------|------------|------------|
|user_id     |prod_1_count |prod_2_count|prod_3_count|prod_4_count|
|------------|-------------|------------|------------|------------|
|1           |0            |2           |0           |1           |
|2           |1            |0           |0           |0           |
|------------|-------------|------------|------------|------------|

Я создаю графики с этими данными, и еще раз (как и сегодня) я не могу подсчитать итоговые значения столбца. Я пробовал:

SELECT user_id, 
(SELECT count(product_id) FROM sales WHERE product_id = 1) AS prod_1_count,
(SELECT count(product_id) FROM sales WHERE product_id = 2) AS prod_2_count,
(SELECT count(product_id) FROM sales WHERE product_id = 3) AS prod_3_count,
(SELECT count(product_id) FROM sales WHERE product_id = 4) AS prod_4_count 
FROM sales GROUP BY user_id;

Я вижу, почему это не работает, потому что для каждого скобочного SELECT user_id не соответствует внешнему user_id в основном операторе SELECT.

Может ли кто-нибудь помочь мне здесь?

спасибо вам

Ответ 1

Вы можете сделать это, используя SUM и CASE:

select user_id,
  sum(case when product_id = 1 then 1 else 0 end) as prod_1_count,
  sum(case when product_id = 2 then 1 else 0 end) as prod_2_count,
  sum(case when product_id = 3 then 1 else 0 end) as prod_3_count,
  sum(case when product_id = 4 then 1 else 0 end) as prod_4_count
from your_table
group by user_id

Ответ 2

Вы пытаетесь свернуть данные. MySQL не имеет сводной функции, поэтому вам придется использовать агрегированную функцию с выражением CASE:

select user_id,
  count(case when product_id = 1 then product_id end) as prod_1_count,
  count(case when product_id = 2 then product_id end) as prod_2_count,
  count(case when product_id = 3 then product_id end) as prod_3_count,
  count(case when product_id = 4 then product_id end) as prod_4_count
from sales
group by user_id;

См. SQL Fiddle with Demo

Ответ 3

Посмотрите, работает ли это:

SELECT a.user_id, 
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 1 AND a.user_id = b.user_id) AS prod_1_count,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 2 AND a.user_id = b.user_id) AS prod_2_count,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 3 AND a.user_id = b.user_id) AS prod_3_count,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 4 AND a.user_id = b.user_id) AS prod_4_count 
FROM sales a GROUP BY a.user_id;

Приветствия. нотабене могут быть несколько более приятные способы достижения эквивалентного результата.