Re: SQL help: tricky statement?

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

Marcel Stör wrote:
> 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...

Found a wonderful solution:

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

Marcel Received on Thu May 01 2003 - 04:36:55 CDT