SQL help: tricky statement?

From: Marcel Stör <marcel>
Date: Thu, 1 May 2003 10:15:40 +0200
Message-ID: <3eb0d7b1$0$28853$4d4ef98e@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 Received on Thu May 01 2003 - 03:15:40 CDT