Conditional Sums

[jonnya]

I have a table with identifier field id (char) and data field c (int). I cant figure out how to create a result set with 3 columns: id, sum(c given c>0), sum(c given c<0).

i thought about the following but it didn't work:

select
id,
case when c>0 then sum(c) as s1 end,
case when c<0 then sum(c) as s2 end
from table
where c <> 0
group by id;

any ideas?

[NogDog]

What DBMS are you using?

[chazzy]

you need some nested selects

give this query a try

SELECT
id, sum(s1), sum(s2)
FROM
(
SELECT
id,
case when c>0 then c else 0 end as s1,
case when c<0 then c else 0 end as s2
FROM table
where c <> 0
order by id
)
group by id;