12

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

user838437
  • 1,451
  • 7
  • 23
  • 31
  • 1
    possible duplicate of [MySQL Query GROUP BY day / month / year](http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) – Salman A May 01 '12 at 08:47
  • 2
    Have 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 May 01 '12 at 08: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 May 01 '12 at 08:51

5 Answers5

21

You can group using the DAY function:

SELECT DAY(Date), COUNT(*)
FROM table
WHERE TID = 8882
GROUP BY DAY(Date)
Sebastien
  • 6,640
  • 14
  • 57
  • 105
alexn
  • 57,867
  • 14
  • 111
  • 145
  • this helped me a lot I was missing the Day() rapper. I actually needed date(), but your answer led me to my solution. +1 – Norman Bird Nov 03 '20 at 19:42
10

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;
Ben
  • 66,838
  • 37
  • 84
  • 108
4

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);
Alex M
  • 2,756
  • 7
  • 29
  • 35
Pankaj Yadav
  • 303
  • 2
  • 7
3

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

Community
  • 1
  • 1
rkosegi
  • 14,165
  • 5
  • 50
  • 83
3

count for every date:

SELECT date(created_at), COUNT(*)
FROM message_requests
GROUP BY date(created_at)
saber tabatabaee yazdi
  • 4,404
  • 3
  • 42
  • 58