Home » SQL & PL/SQL » SQL & PL/SQL » aggregating row data
aggregating row data [message #224183] Tue, 13 March 2007 06:36 Go to next message
aankha
Messages: 8
Registered: January 2007
Location: Nepal
Junior Member
I have a large amount of data, taken at the period of 15 minutes. I have to create a report that aggregates the data for an hour. How do i do that?

I tried reading through the posts in the forum. But I am not quite sure what to search for. Help!!
Re: aggregating row data [message #224275 is a reply to message #224183] Tue, 13 March 2007 12:38 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
SQL> create table t1 (col1 date, col2 number);

Table created.

SQL> insert into t1 values (to_date('01-01-07 14:02:00', 'dd-mm-rr hh24:mi:ss'), 10);

1 row created.

SQL> insert into t1 values (to_date('01-01-07 14:17:00', 'dd-mm-rr hh24:mi:ss'), 20);

1 row created.

SQL> insert into t1 values (to_date('01-01-07 14:31:00', 'dd-mm-rr hh24:mi:ss'), 30);

1 row created.

SQL> insert into t1 values (to_date('01-01-07 14:47:00', 'dd-mm-rr hh24:mi:ss'), 40);

1 row created.

SQL> select to_char(col1, 'dd-mm-rr hh24:mi:ss'), col2 from t1;

TO_CHAR(COL1,'DD-       COL2
----------------- ----------
01-01-07 14:02:00         10
01-01-07 14:17:00         20
01-01-07 14:31:00         30
01-01-07 14:47:00         40

SQL> select to_char(trunc(col1, 'hh'), 'dd-mm-rr hh24:mi:ss'), sum(col2)
  2  from t1
  3  group by to_char(trunc(col1, 'hh'), 'dd-mm-rr hh24:mi:ss');

TO_CHAR(TRUNC(COL  SUM(COL2)
----------------- ----------
01-01-07 14:00:00        100

SQL> 
Previous Topic: lost updates
Next Topic: Top / Bottom Query - Oracle 00907 Error
Goto Forum:
  


Current Time: Fri Dec 09 15:52:59 CST 2016

Total time taken to generate the page: 0.20635 seconds