Re: Is there a slick and efficient way to do this query?
Posted in 1999
Topics: SQL Development & Query Writing
Seems great and slick IMHO! Thanks.
Now I'm going to try and understand how it works!!
Paul Roberts wrote in message <79sn9i$etg2@webint.na.informix.com>...
>>For example
>>
>>Days Total_Days Cumulative Days
>>2 10 2
>>3 10 7
>>5 10 10
>>
>>
>>Can anyone think of a slick and efficient way to do it, just using SQL?
>>
>>Remember the differing values that days takes on in the first table is an
>>unknown.
>>
>>
>>
>
>Dunno is you would count this as slick and efficient, but:
>
>create temp table mytable ( days integer ) ;>
>insert into mytable values ( 2 ) ;
>insert into mytable values ( 3 ) ;
>insert into mytable values ( 5 ) ;>
>select mt1.days, ( select sum(days) from mytable), sum(mt2.days)
> from mytable mt1, mytable mt2
> where mt2.days <= mt1.days
> group by 1,2
> order by 1>
>gives you:
>
> days (expression) (sum)
>
> 2 10 2
> 3 10 5
> 5 10 10
>
>(did you mean 5 instead of 7?)
>
> - Paul