Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql question
cazman wrote:
> Hi all,
>
> Let's say I have the fallowing table:
>
> tbl 1
>
> col1 col2 col3 val
> 1 2 s 45
> 1 2 k 84
> 1 2 p 42
> 4 5 s 96
> 4 5 k 66
> 4 5 p 44
>
>
> I would like to write a query to get the fallowing result:
>
> col1 col2 res
> 1 2 (s-k /p)
> 4 5 (s-k /p)
> (s-k /p): this is a math operation, taking the val column for the
> respective s, k and p values.
>
> So, the result would be:
>
> col1 col2 res
> 1 2 -0.928
> 4 5 0.6818
>
>
>
> Thanks!
SQL92 but tested on MSSQL 2000 so no guarantees for your (unspecified) version of Oracle:
SELECT col1, col2,
SUM(CASE col3
WHEN 's' THEN val
WHEN 'k' THEN -val END)
/SUM(CASE WHEN col3 = 'p' THEN val END) AS res
FROM tbl1
GROUP BY col1, col2
Hope this helps.
-- David Portas SQL Server MVP --Received on Tue May 24 2005 - 11:17:06 CDT