CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Learning
Learning

Reputation: 20001

Get Count of users based on date

I have simple table where i store userID, UserName, Email, RegistrationDate ...

RegistrationDate is DateTime Datatype

When i to to get the count of users for each date it get me 1 for each row as it each row has a unique dateTime stamp.

SELECT DATEPART(dd, RegistrationDate) AS DD, COUNT(userID)   
FROM USER_Table 
GROUP BY RegistrationDate 
order by RegistrationDate DESC

ABove query get me each 100 rows if i have 100 users registered.

I want to get count based on date. i tried different thing but nothing seems to work.

Upvotes: 0

Views: 148

Answers (3)

Milen
Milen

Reputation: 8867

You need to group by CAST(RegistrationDate as Date)

SELECT CAST(RegistrationDate as Date) AS [Date], COUNT(userID)   
FROM USER_Table 
GROUP BY CAST(RegistrationDate as Date)
order by CAST(RegistrationDate as Date) DESC

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

SELECT cast(RegistrationDate as Date) AS DD, COUNT(userID)   
FROM USER_Table 
GROUP BY cast(RegistrationDate as Date)
order by cast(RegistrationDate as Date) DESC

Upvotes: 0

Deep
Deep

Reputation: 3202

Try this :

SELECT Cast(RegistrationDate AS DATE),
       Count(userID)
FROM   User_Table
GROUP  BY Cast(RegistrationDate AS DATE)
ORDER  BY Cast(RegistrationDate AS DATE)DESC

Upvotes: 3

Related Questions