Re: SQL help: tricky statement?

"Marcel Stör" <marcel at frightanic dot com> wrote in message news:3eb0d7b1$0$28853$4d4ef98e_at_read.news.ch.uu.net...
> Hi all,
>
> Hope someone can help me with this statement....
> The simplified situation is this:
> view XY with columns
> customer
> time number
> allocated {0|1}
>
> What I am looking for is the appropriate statement to build a view with
four
> columns:
> customer
> sum(time) where allocated = 0
> sum(time) where allocated = 1
> sum(time) without allocated constraint -> sum of column 2 and 3
>
> My first try was a statement that uses 3 aliases for view XY:
> select p.customer,
> sum(p.time),
> sum(p1.time),
> sum(p2.time)
> from XY p,
> XY p1,
> XY p2
> where p.customer = p1.customer
> and p1.customer = p2.customer
> and p.allocated = 0
> and p1.allocated = 1
> group by p.customer
>
> Needless to mention that it didn't produce the desired output...
>
> Regards,
> Marcel
>
>

A possible solution :

Select
XYA.customer, XY0.t t0, XY1.t t1, XYA.t ta FROM
(Select customer, sum(time) t from XY where allocated = 0 group by customer) XY0,
(Select customer, sum(time) t from XY where allocated = 1 group by customer) XY1,
(Select customer, sum(time) t from XY group by customer) XYA Where
XYA.customer = XY0.customer
And
XYA.customer = XY1.customer;

It worked on my 9.2.0.2 database

It is always useful to post database & operating system versions.

eric

PS. Not sure about the use of the word 'time' for a column name. It might be reserved somewhere in Oracle. Received on Thu May 01 2003 - 04:32:44 CDT