Re: SQL help: tricky statement?

From: andrewst <member14183_at_dbforums.com>
Date: Thu, 01 May 2003 10:00:13 +0000
Message-ID: <2828612.1051783213@dbforums.com>

Originally posted by Marcel StöR
> 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

This can be done using either DECODE or CASE:

select xy.customer,

       sum( case when xy.allocated = 0 then xy.time else 0 end ) t0,
       sum( case when xy.allocated = 1 then xy.time else 0 end ) t1,
       sum(xy.time) t

from xy
group by xy.customer;

select xy.customer,

       sum( decode(xy.allocated,0,xy.time,0)) t0,
       sum( decode(xy.allocated,1,xy.time,0)) t1,
       sum(xy.time) t

from xy
group by xy.customer;

--
Posted via http://dbforums.com

Received on Thu May 01 2003 - 05:00:13 CDT