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
Thanks
-
1possible duplicate of MySQL Query GROUP BY day / month / yearSalman Arshad– Salman Arshad2012-05-01 08:47:34 +00:00Commented May 1, 2012 at 8:47
-
2Have u tried anything? or u r just waiting for whole code? and what do u mean by group them by each day? u mean group by date?sujal– sujal2012-05-01 08:48:12 +00:00Commented May 1, 2012 at 8:48
-
when you say "group by day" ... do you mean all items within a single date ? or you want to group items that fall into the same day of the month of different months (e.g. 1 Jan and 1 Feb, and 1 March together) ?Aziz– Aziz2012-05-01 08:51:06 +00:00Commented May 1, 2012 at 8:51
Add a comment
|
5 Answers
You can group using the DAY function:
SELECT DAY(Date), COUNT(*)
FROM table
WHERE TID = 8882
GROUP BY DAY(Date)
1 Comment
Norman Bird
this helped me a lot I was missing the Day() rapper. I actually needed date(), but your answer led me to my solution. +1
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:
SELECT DATE(date) as d,count(ID) from TABLENAME where TID=8882 GROUP by d;
Comments
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);
Comments
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
Comments
count for every date:
SELECT date(created_at), COUNT(*)
FROM message_requests
GROUP BY date(created_at)
answered Sep 16, 2021 at 9:12
saber tabatabaee yazdi
4,9953 gold badges53 silver badges63 bronze badges