CODEWITHSUNDEEP
mysqlgroup-bytimestamp
Zaid Kajee
Zaid Kajee

Reputation: 712

Group by the date part of a timestamp field

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?

Upvotes: 16

Views: 26410

Answers (3)

jmail
jmail

Reputation: 6134

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

Upvotes: 23

PravinS
PravinS

Reputation: 2584

use query like this

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

Upvotes: 1

Sathish
Sathish

Reputation: 4487

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 );

Upvotes: 1

Related Questions