'How to calculate gross value of a column containing "null", positive and negative values on SQL Server?

I have the following table in SQL Server:

![![![enter image description here

I am trying to group the table by columns "Portfolio" and "Date" and sum up Net Discrepancy. Also, I would like to add a new column named "Gross Discrepancy".

The "Gross Discrepancy" would be the same as NET Discrepancy if NET Discrepancy is a positive number. If Net Discrepancy is a negative number, then it should be multiplied by -1 in order to get a positive number. In short, I am looking for the result like below:

![enter image description here

I have been written the code below, which correctly calculates the aggregate NET_discrepancy but I am not able to figure out how to get the GROSS_discrepancy:

SELECT 
    date_long, portfolio, 
    SUM(ISNULL(CAST(NET_Discrepancy AS float), 0))
FROM 
    My_Table
GROUP BY
    date_long, Portfolio
ORDER BY
    date_long
sql-serversumcase


Solution 1:[1]

You have to use the ABS function to get an absolute value Result here

select 
portfolio,
date_long,
sum(coalesce(net_discrepancy,0)) as "NET discrepancy",
abs(sum(coalesce(net_discrepancy,0))) as "GROSS discrepancy"
from my_table
group by portfolio,date_long
order by 1 desc,2 desc

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Philippe

Related Questions