CODEWITHSUNDEEP
sqlsql-server-2012
user5192770
user5192770

Reputation: 21

sqlserver sum with minus

I use sqlserver 2012. I have a query like this

SELECT SUM(TH.CLEAVE_EARN_DAY), SUM(TH.CLEAVE_DAY), 
       SUM(TH.CLEAVE_EARN_DAY) - SUM(TH.CLEAVE_DAY) 
FROM TH_LEAVE_CARD TH

The result is 0, 14.5, -15 so -15 is wrong. Must be -14.5

any suggestion ?

Upvotes: 1

Views: 181

Answers (4)

mokh223
mokh223

Reputation: 624

There is few reason why the result is not as per what you are expecting. In Sql Server any math operation that contains a null would result to null. for example sum(1,2,3,null,4) is equal to null. 1 + null also equal to null.

therefore it would be safer to use isnull function to assign a default value in case the value is null.

for mathematical operation. sql server would do the calculation based on the specified data type. for example int / int = int. therefore the result would be missled. because most of the time int / int = float.

it would be better to change the value to double prior to do any arithmetic operation.

below is the example after include the isnull function as well as cast to float.

SELECT SUM(CAST(ISNULL(TH.CLEAVE_EARN_DAY,0) as double)), SUM(cast(ISNULL(TH.CLEAVE_DAY,0) as double)), 
       SUM(cast(ISNULL(TH.CLEAVE_EARN_DAY,0) as double)) - SUM(cast(ISNULL(TH.CLEAVE_DAY,0) as double)) 
FROM TH_LEAVE_CARD TH

Upvotes: 0

P. K. Singh
P. K. Singh

Reputation: 1

To perform mathematical operations on columns:

  1. Used columns should be converted into same numeric/decimal data type.

  2. To handle null values you may use ISNULL function. Ex: 

    SELECT SUM(TH.CLEAVE_EARN_DAY), SUM(TH.CLEAVE_DAY), 
   SUM(cast  (TH.CLEAVE_EARN_DAY) as decimal(5,1)) - SUM(cast ( (TH.CLEAVE_DAY) as decimal(5,1))
FROM TH_LEAVE_CARD

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176174

Try converting all arguments to the same datatype and then do calculation:

SELECT
  SUM(CAST(TH.CLEAVE_EARN_DAY AS DECIMAL(18,2))),
  SUM(CAST(TH.CLEAVE_DAY AS DECIMAL(18,2))), 
  SUM(CAST(TH.CLEAVE_EARN_DAY AS DECIMAL(18,2)) 
    - CAST(TH.CLEAVE_DAY AS DECIMAL(18,2))) AS substraction 
FROM TH_LEAVE_CARD TH

Also you can combine:

SUM(TH.CLEAVE_EARN_DAY) - SUM(TH.CLEAVE_DAY)

to (if both column are NOT NULL):

SUM(TH.CLEAVE_EARN_DAY - TH.CLEAVE_DAY)

or (thanks Arvo for pointing this):

SUM(ISNULL(TH.CLEAVE_EARN_DAY,0) - ISNULL(TH.CLEAVE_DAY,0))

Upvotes: 0

Veljko89
Veljko89

Reputation: 1953

This is what you can try

SELECT SUM(TH.CLEAVE_EARN_DAY), SUM(TH.CLEAVE_DAY), 
   SUM(TH.CLEAVE_EARN_DAY)*1.0 - SUM(TH.CLEAVE_DAY) 
FROM TH_LEAVE_CARD TH

Multiplying with 1.0 will just give you back decimal value and taking away will give you what you asked for

Upvotes: 3

Related Questions