?
Solved

sql query group by hour

Posted on 2014-01-02
2
Medium Priority
?
3,523 Views
Last Modified: 2014-01-06
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
0
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39752756
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39753359
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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question