Oracle SQL Query with Additional Summation Columns

Question

Suppose I have a table that has USER_ID and a AMOUNT columns. For each row, there could be a positive or negative amount in the AMOUNT column.

I'm looking for a way to select from this table such that the result has the id, summation of the amount, summation of the amount of just the positive values, summation of amount of just the negative values for each USER_ID. I can do the summation one quite easily, but I'm trying to figure out how to do the last two columns.

I'm still fairly new to SQL. I know some of the basics but I'm stumped on this one. I have a feeling I can't do this with a single SELECT statement. Any help would be great.

Answer 1

The LEAST and GREATEST Oracle functions will do the trick (not sure wheather they are ANSI or Oracle only ):

select
    user_id
    sum(amount) total,
    sum(least(amount,0)) total_negs,
    sum(greatest(amount,0)) total_pos
from
    my_table
group by
    user_id

LEAST returns the smallest value from a list of parameters. If you pass amount and zero, it will filter positive numbers out.

GREATEST will filter out negative numbers.

This solution will not work with averages since zeroes will alter the average.

Answer 2

select
    user_id
    sum(amount) total,
    sum(case when amount < 0 then amount else 0 end) total_negs,
    sum(case when amount > 0 then amount else 0 end) total_pos
from
    my_table
group by
    user_id

Something like this you need.