Group by the date part of a timestamp field

Question

I have users table. I would like to generate a report of user who joined for that day. Problem is, my DateJoined field is a timestamp field,

As the query is following as:

SELECT COUNT(UserID) AS TOT FROM users GROUP BY DateJoined

does not work, how do I get it to GROUP BY just the date part and not the time of DateJoined field?

@jmail: I appreciate your intention to format posts to make them convey the message properly to the end user. But I still suggest you to sign up at [*English Language Learners*](http://ell.stackexchange.com/) and gain some reputation. — Ravinder Reddy, Mar 24 '14 at 11:05
@Ravinder, thanks for suggestion And definitely I'll sign up in the `ELL`.. — jmail, Mar 24 '14 at 11:14
Possible duplicate of [MySQL/SQL: Group by date only on a Datetime column](http://stackoverflow.com/questions/366603/mysql-sql-group-by-date-only-on-a-datetime-column) — Oleg V. Volkov, Apr 20 '17 at 14:18

score 22 · Accepted Answer · edited Nov 07 '17 at 23:04

22

your code:

SELECT 
      COUNT(UserID) AS TOT 
      FROM users 
      GROUP BY DateJoined

you should change like as:

SELECT 
       DATE(DateJoined), 
       COUNT(UserID) AS TOT 
       FROM users 
       GROUP BY DATE(DateJoined)

the sqlfiddle

edited Nov 07 '17 at 23:04

EternalHour

answered Mar 24 '14 at 10:18

jmail

score 1 · Answer 2 · answered Mar 24 '14 at 10:05

1

Try Like This

 SELECT COUNT(UserID) AS TOT FROM users GROUP BY CAST(DateJoined AS DATE)

OR

SELECT COUNT(UserID) AS TOT, FROM a_table GROUP BY DATE(DateJoined );

answered Mar 24 '14 at 10:05

Sathish

score 1 · Answer 3 · answered Mar 24 '14 at 10:05

1

use query like this

SELECT COUNT(UserID) AS TOT FROM users GROUP BY DATE(DateJoined)

answered Mar 24 '14 at 10:05

PravinS

3 Answers3