Re: SQL help: tricky statement?

From: Marcel Stör <marcel>
Date: Thu, 1 May 2003 11:36:23 +0200
Message-ID: <3eb0ea9d$0$28853$4d4ef98e@read.news.ch.uu.net>

Eric Parker wrote:
> "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.

Someone else helped my out with this one. It's great!

SELECT customer,

SUM(CASE WHEN allocated=0 THEN time END),
SUM(CASE WHEN allocated=1 THEN time END),
SUM(time)

FROM XY
GROUP BY customer

> PS. Not sure about the use of the word 'time' for a column name. It
> might be reserved somewhere in Oracle.

Don't worry, the actual column has a different name...

Thanks,
Marcel Received on Thu May 01 2003 - 04:36:23 CDT