MySQL MySQL按天计数和分组

MySQL count and group by day

提问by user838437

I have the following structure

我有以下结构

ID    DATE(DATETIME)         TID
1     2012-04-01 23:23:23    8882

I'm trying to count the amount of rows and group them by each day of the month that matches TID = 8882

我正在尝试计算行数并按与 TID = 8882 匹配的月份中的每一天对它们进行分组

Thanks

谢谢

回答by alexn

You can group using the DAYfunction:

您可以使用DAY函数进行分组：

SELECT DAY(Date), COUNT(*)
FROM table
WHERE TID = 8882
GROUP BY DAY(Date)

回答by Ben

Not sure exactly what you mean by day of the month -- do you want to group the 1st of Feb with the 1st of March? Or do you just mean date? Assuming the latter, how about this:

不确定您所说的一个月中的某一天是什么意思——您想将 2 月 1 日与 3 月 1 日归为一组吗？或者你只是说约会？假设是后者，这个怎么样：

SELECT DATE(date) as d,count(ID) from TABLENAME where TID=8882 GROUP by d;

回答by Pankaj Yadav

Try this query:

试试这个查询：

SELECT COUNT(id), DAY(dat), MONTH(dat), YEAR(dat) 
FROM table
WHERE TID=8882
GROUP BY YEAR(dat), MONTH(dat), DAY(dat);

回答by rkosegi

Try this:

尝试这个：

SELECT DAY(date) AS `DAY`,  COUNT(1) AS `COUNT` FROM
table1 
    WHERE TID = 8882
GROUP BY DAY(date)

What about MySQL Query GROUP BY day / month / year

MySQL Query GROUP BY 天/月/年怎么样？