Have Geek Brain

Remove "-" from SUM column

Group, I need to remove "-" from any negative numbers in a column for specific record numbers. However, Sum should * * .01 get the correct format. I tried using replace but it gets discarded * .01 Below is my syntax.

CASE WHEN SUM(ExtPrice) *.01 < 0 AND RecordNum BETWEEN 4000 AND 5999 
        THEN REPLACE(SUM(ExtPrice) *.01,'-','')
     ELSE SUM(ExtPrice) *.01 
END AS Totals

For example, SUM(ExtPrice) *.01

one column gives me -5051.32, but when I use the above case statement, I get 5050, another example is -312.67, and I get 310 using case. Any suggestions or better ways to do this are greatly appreciated.

0


a source to share


1 answer


You can use the ABS function to get a positive value for a number. For instance:

ABS(-123.445) /* this equals 123.445 */


So, you can replace your CASE statement with the following:

CASE WHEN SUM(ExtPrice) < 0 AND RecordNum BETWEEN 4000 AND 5999         
         THEN ABS(SUM(ExtPrice) *.01)     
     ELSE SUM(ExtPrice) *.01 
END AS Totals
+9


a source






More articles:


All Articles