查询Count on coulmns by condition with Group BY
查询Count on coulmns by condition with Group BY
提问于 2012-01-07 18:58:23
表结构
id(int)
dated_on(datetime)
(many_more)包含数千个按id分组的数据的表。
I want result as id, count of that id,
also count of id having date difference is week.(i.e. count of data last week)回答 3
Stack Overflow用户
回答已采纳
发布于 2012-01-07 19:02:13
select
id,
count(id) as cnt,
sum(if(now() - interval 7 day <= dated_on,1,0)) as lastweek
from table
group by idStack Overflow用户
发布于 2012-01-07 19:02:39
SELECT
id,
COUNT(*),
SUM(CASE WHEN dated_on = CURDATE() - 7 THEN 1 ELSE 0 END)
FROM
yourTable
GROUP BY
idStack Overflow用户
发布于 2012-01-07 20:19:39
我也得到了解决方案：
SELECT id, count(id) as counter,
COUNT(CASE WHEN DATEDIFF( CURDATE(), dated_on ) < 7 THEN 1 ELSE NULL END) AS latest_count
FROM my_table
GROUP BY id 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8769142
复制相关文章
相似问题
腾讯云开发者
