CODEWITHSUNDEEP
sql-server
pavan kukalakuntla
pavan kukalakuntla

Reputation: 11

How could i replace negative values in this column to 0

SUM([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS DAmount

how can i replace negative values with 0 in this column i tried 

select sUM(IF ([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]  < 0, 0, X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)] )) as DAmount

Upvotes: 1

Views: 3492

Answers (4)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

I have noticed there are multiple answers with case statements but there is another option which might interest you (using a where clause).

Select SUM([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS DAmount
FROM Table
Where [X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]>0

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You could use case when

select sum(case when [X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)] >0 
  then [X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]  end ) as DAmount

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use case when

select case when SUM([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)])<0 then 0 else SUM([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) end
  AS DAmount

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use a case expression:

(CASE WHEN SUM([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) > 0
      THEN SUM([X Limited$Detailed Cust_ Ledg_ Entry].[Amount (LCY)])
      ELSE 0
 END) AS DAmount

Upvotes: 1

Related Questions