sql query group by hour

I have a table named LogEntries that has the following structure




Logdatetime (datetime)

Entries (integer)
Exits (integer)


Here is some sample data


LogDateTime                        Entries      Exits
2013-12-20 08:30:00.000      0                 3      
2013-12-20 08:35:00.000      5                  2
2013-12-20 08:55:00.000      12               7
2013-12-21 09:20:00.000      10                 3      
2013-12-21 09:35:00.000      6                 2
2013-12-21 09:59:00.000      27               4
2013-12-23 17:20:00.000      6                1
2013-12-23 17:48:00.000      6                2
2013-12-23 17:59:00.000      7               3


Trying to write a query that will return sum of entries and exists by date and hour of date

using example above I am looking for following output

Logdate               Hour                 entries    exits

2013-12-20               8                       17                 12      
2013-12-21               9                       43              9
2013-12-23              17                    19              6
johnnyg123Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chaauConnect With a Mentor Commented:
Yes, it is possible:
select CAST(LogDateTime AS Date) as LogDate
, datepart(hh, LogDateTime) as Hour
, Sum(Entries) as Entries
, Sum(Exits) as Exits
FROM Table1
Group By CAST(LogDateTime AS Date)
, datepart(hh, LogDateTime)

Open in new window

0
 
SurranoSystem EngineerCommented:
Using dateadd:
select dateadd(hour, datediff(hour, 0, logdatetime), 0) as loghour,
  sum(entries) as entries, sum(exits) as exits)
from LogEntries 
-- where <any condition>
group by dateadd(hour, datediff(hour, 0, logdatetime), 0) 
order by dateadd(hour, datediff(hour, 0, logdatetime), 0);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.