MySQL计数where和COUNT all

Question

我有一个表(sales)，它有四列；

id, user_id, product_id, and date_added.

我需要统计某个用户已经售出的具有某个id的产品的数量，并获得该用户在当前月份售出的产品的总数。

我不能把所有的产品加起来。我有；

SELECT user_id, product_id, count(user_id) 
FROM sales 
WHERE MONTH(date_added) = MONTH(CURRENT_DATE) 
GROUP BY user_id, product_id; 

如何将用户的总销售额相加，以便获得特定product_id的总销售额的百分比？

我希望我的输出看起来像这样；

|----------|----------|----------|----------|
|user_id   |product_id|sales     |total     |
|----------|----------|----------|----------|
|1         |4         |6         |82        |
|2         |4         |3         |121       |
|3         |4         |8         |93        |      
|----------|----------|----------|----------|

其中total是用户进行的销售总数。

(值得一提的是，我不需要mysql来计算百分比-只是为了获得所有产品销售额的总和)

谢谢

Answer 1

您必须使用子查询，因为您依赖聚合函数来执行计数

SELECT user_id, @pid:= product_id as product_id, count(user_id),
         (SELECT count(*)
          FROM sales
          WHERE MONTH(date_added) = MONTH( now() )
               AND product_id = @pid
          group by user_id
         ) as total_product_sales
FROM sales 
WHERE MONTH(date_added) = MONTH( now() ) 
GROUP BY user_id, product_id; 

Answer 2

其实我不太明白你想要什么.

此查询将为您提供给定用户的“小计”

 SELECT product_id, count(user_id) 
 FROM sales 
 WHERE MONTH(date_added) = MONTH(CURRENT_DATE) 
   AND user_id='YOUR_USER_ID'
 GROUP BY product_id with rollup; 

或者，如果您想要给定当月的产品id和用户id的总体百分比：

 SELECT 
        PARTIAL_SALES/TOTAL_SALES*100 
 FROM
      (SELECT USER_ID,
              count(user_id) AS PARTIAL_SALES
         FROM sales 
              WHERE MONTH(date_added) = MONTH(CURRENT_DATE) 
              AND user_id='YOUR_USER_ID'
              AND PRODUCT_ID='YOUR_PRODUCT_ID'
          GROUP BY USER_ID) AS PARTIAL,
      (SELECT USER_ID, 
              count(user_id) as TOTAL_SALES
         FROM sales 
              WHERE MONTH(date_added) = MONTH(CURRENT_DATE) 
          GROUP BY USER_id) AS TOTAL1
 WHERE PARTIAL.USER_ID=TOTAL.USER_ID;