Re: Help: SQL Question

> I have a table like this:
>
> create table t (
> time date not null,
> count number not null
> )
>
> Data in this table like this: ( time is in format 'YYYY-MM-DD HH24:MI' )
>
> time count
> ---------------------------------
> 2002-1-1 0:0 100
> 2002-1-1 0:1 45
> 2002-1-1 0:3 20
> 2002-1-1 0:5 742
> 2002-1-1 0:6 235
> 2002-1-1 0:10 124
> 2002-1-1 0:11 23
> 2002-1-1 0:12 23
> 2002-1-1 0:18 577
> 2002-1-1 0:21 124
> 2002-1-1 0:22 23
> 2002-1-1 0:25 23
> 2002-1-1 0:31 577
> ......
>
> I want to calculate the sum(count) of every 15 minutes, for example:
>
> summary of 2002-1-1 0:0 100
> 2002-1-1 0:1 45
> 2002-1-1 0:3 20
> 2002-1-1 0:5 742
> 2002-1-1 0:6 235
> 2002-1-1 0:10 124
> 2002-1-1 0:11 23
> 2002-1-1 0:12 23
>
> summary of 2002-1-1 0:18 577
> 2002-1-1 0:21 124
> 2002-1-1 0:22 23
> 2002-1-1 0:25 23
>
> summary of 2002-1-1 0:31 577
> ......
>
> What should I do? Is there anyone who can provide a solution?
> Thank you.

What about

select to_char(time,'YYYY-MM-DD') "Date", to_char(time,'HH24') "Hour", 'first quarter' "Quarter", sum(count) "Sum" from t where
to_char(time,'MI')<='15'
group by to_char(time,'HH24'), to_char(time,'YYYY-MM-DD') union
select to_char(time,'YYYY-MM-DD') "Date", to_char(time,'HH24') "Hour", 'second quarter' "Quarter", sum(count) "Sum" from t where
to_char(time,'MI')<='30' and to_char(time,'MI')>'15' group by to_char(time,'HH24'), to_char(time,'YYYY-MM-DD') union
select to_char(time,'YYYY-MM-DD') "Date", to_char(time,'HH24') "Hour", 'third quarter' "Quarter", sum(count) "Sum" from t where
to_char(time,'MI')<='45' and to_char(time,'MI')>'30' group by to_char(time,'HH24'), to_char(time,'YYYY-MM-DD') union
select to_char(time,'YYYY-MM-DD') "Date", to_char(time,'HH24') "Hour", 'forth quarter' "Quarter", sum(count) "Sum" from t where
to_char(time,'MI')>'45'
group by to_char(time,'HH24'), to_char(time,'YYYY-MM-DD');

There are easier ways but this is just a quick hack and I'm sure you get what you need to work on it.

Regards,

Knut Received on Mon Jan 21 2002 - 06:19:30 CST