CODEWITHSUNDEEP
sqloracle-databaseoracle11gtimestamp
user206168
user206168

Reputation: 1025

SQL SUM DATA by Hour

I have 3 columns

ID
TIMESTAMP
CALLS

I need to sum up number of call by hour. example

ID         TIMESTAMP          CALLS
1     10-01-2013 01:00:23       23
2     10-01-2013 03:00:23       55
3     10-01-2013 05:00:23       2369
4     10-01-2013 04:00:23       12
5     10-01-2013 01:00:23       1
6     10-01-2013 12:00:23       99
7     10-01-2013 01:00:23       22
8     10-01-2013 02:00:23       3

Output result should be something like

 TimestampHOUR       Totalcalls
  10/1/2013 01:00       46
  10/1/2013 02:00       3
  10/1/2013 03:00       55
  10/1/2013 04:00       12
  10/1/2013 05:00       2369
  10/1/2013 11:00       99

I already tried used datepart,hour(timestamp) but I guess it is not supported by my version of oracle.

Upvotes: 1

Views: 5672

Answers (2)

Mureinik
Mureinik

Reputation: 312219

SELECT   TRUNC(timestamp, 'HH24') AS hour, COUNT(*) AS TotalCalls
FROM     some_table
GROUP BY TRUNC(timestamp, 'HH24')

Upvotes: 2

dani herrera
dani herrera

Reputation: 51715

You should trunc your date time to hour, then group. Sample rounding to hour:

select TRUNC (sysdate,'hh') from dual

see results

Upvotes: 1

Related Questions