CODEWITHSUNDEEP
sql-servert-sql
chinnusaccount
chinnusaccount

Reputation: 229

How can I group data on the basis of date and hour in ms-sql?

I have following data in my table activity_count

Activitydate        |count
2013-12-20:18:25:45  10
2013-12-20:18:23:40  20
2013-12-20:17:25:45   5
2013-12-20:17:25:45  10
2013-12-20:17:25:45  10

I want to get the total counts for each hour,ie the result should be following

Activitydate        |count
2013-12-20:18:00:00   30
2013-12-20:17:00:00   25

Upvotes: 3

Views: 8371

Answers (4)

Edper
Edper

Reputation: 9322

If you're using mySQL try below:

SELECT CONCAT(LEFT(DATE(activitydate),10),' ',HOUR(activitydate),':00:00') as DateHour,
SUM(count) as TotalCount
FROM activity_count
GROUP BY DATE(activitydate),HOUR(activitydate)

See my Demo.

However, if MSSQL try this one:

SELECT CAST(CAST(activitydate as DATE) AS nvarchar(15))+' '+CAST(datepart(HOUR,activitydate) as CHAR(2))+':00:00' as DateHour,
SUM([count]) as TotalCount
FROM activity_count
GROUP BY CAST(CAST(activitydate as DATE) AS nvarchar(15)),DATEPART(HOUR, activitydate)

See MSSQL Demo

Upvotes: 3

Nitu Bansal
Nitu Bansal

Reputation: 3856

please try this

  select cast(cast(Activitydate as date) as datetime)+cast(datepart(hour,Activitydate),
  count(*)
   from activity_count 
  group by cast(cast(Activitydate as date) as datetime)+cast(datepart(hour,Activitydate)

Upvotes: 1

PSR
PSR

Reputation: 40318

HOUR(time)

 HOUR(time)

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try HOUR function

 select 
Activitydate,        
count(*) as 'count'
from activity_count
GROUP BY HOUR(Activitydate)

Upvotes: 0

Related Questions