Mysql的总和 - 计数，与组，为了通过，限制功能

Question

我有表USER_VIEWS

我的平板电脑的样本数据

Id | product_id | category | user_id | sitename| price | click_count | created_date 
1  10   watch  102   ebay  820  1   2014-08-18 13:56:05 
2  10   watch  102   amazon  750  1   2014-08-19 13:56:05 
3  10   watch  102   amazon  740  1   2014-08-19 18:00:05 
4  10   watch  102   ebay  940  1   2014-08-25 08:00:00 
5  10   watch  102   amazon  640  5   2014-08-25 08:10:10 
6  10   watch  102   ebay  580  3   2014-09-25 18:10:10 
7  10   watch  102   amazon  980  5   2014-10-05 12:20:40 

我希望用户的总数访问本产品

我的查询

"select Id , proudct_id , category , user_id , count(click_count) as cnt from user_view where user_id =102 group by product_id order by rand() limit 0,10" 

但产量只显示一个计数

输出

Id | product_id | category | user_id | cnt 
    1  10   watch  102  1   

预期成果是

Id | product_id | category | user_id | cnt 
    1  10   watch  102  17 

Answer 1

您应该使用SUM聚合函数，而不是数量，这将计算行数。所以，你的查询应该是这样的：

select Id , product_id , category , user_id , SUM(click_count) as sum ... 

Answer 2

试试这个：

select Id , proudct_id , category , user_id , SUM(click_count) as cnt 
from user_view 
where user_id =102 
order by rand() 
limit 0,10 
group by product_id 

COUNT()总是返回项目的数量，而不是值的总和。所以使用SUM()。

Answer 3

"select Id , proudct_id , category , user_id , count(click_count) as cnt from user_view where user_id =102 group by user_id, product_id order by rand() limit 0,10"