SQL associate - signs in one column with amount in another column and aggregate by date-CodePudding

I have this table:

date	ammount	sign
2022-07-01	200
2022-07-01	300
2022-07-02	100	-
2022-07-02	150

And I want this one:

date	ammount
2022-07-01	500
2022-07-02	50

CodePudding user response：

You could use CASE expression

SELECT date, 
    SUM(CASE WHEN sign = ' ' THEN ammount WHEN sign = '-' THEN -ammount END) AS sum_amount
FROM table_name
GROUP BY date;  --TRUNC(date)   -- CONVERT(varchar(12), date, 101)

CodePudding user response：

You try this:

select date1, sum(sign||amount) 
 from mytable
group by date1;

Thank you.