I see your "another way" and raise you "an even other way"
select sum(ABS(id)+id)/2 as pos_sum,
sum(ABS(id)-id)/2 as neg_sum
(but I think I'm losing in the performance department
The real silly question will be ... how crazy a solution can you come up with for something like this lol
Your original idea (that is, "SUM (CASE ...") is the best general solution
If we needed a sub-query anyway (which is often the case), then "SELECT ... PIVOT" would be a good alternative:
WITH got_id_sign AS
, SIGN (id) AS id_sign
PIVOT ( SUM (id)
FOR id_sign IN ( 1 AS posi_id
, -1 AS neg_id
If the only reason for a sub-query is to get id_sign, however, then it's an awful lot of extra coding.
If output on 2 separate rows (rather than 2 separate columns) is okay:
SELECT CASE SIGN (id)
WHEN 1 THEN 'POSI_ID'
WHEN -1 THEN 'NEG_ID'
END AS pos_or_neg
, SUM (id) AS total
GROUP BY SIGN (id)
Of course, the pos_or_neg column isn't really needed; you can tell which is which by the sign of the total.