sqlserver sum with minus

how to subtract two values in sql
how to subtract two queries in sql
sql minus vs except
calculation in sql query
sql subtract sum of two columns
sql sum
sql subtract two columns different tables
sql divide

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 ?

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

This SQL tutorial explains how to use the SQL MINUS operator with syntax and examples. The SQL MINUS operator is used to return all rows in the first SELECT​  The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

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))

Arithmetic operators are addition(+), subtraction(-), multiplication(*) and The SQL plus (+) operator is used to add two or more expressions or numbers. 1. sum of 'opening_amt' and 'receive_amt' is greater than 15000,. The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query.

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 
    

The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the​  SQL MINUS query or SQL EXCEPT query is used to subtract out the result of second query from the result of the first qeury. It takes the distinct result set of the first query, then filter out the records which appear in the result set of the second query.

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 

Hide Copy Code. SELECT A.REGISTRATION_ID, A.Fee_type, A.AMOUNT, B.​AMOUNT AS PAID, (A.AMOUNT - B.AMOUNT) AS DUE FROM  Absolutely, EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

HI ! I am trying to subtract the sum of two different columns on two different tables from each other. If I write my sql as: Select  Introduction to SQL MINUS operator. Besides the UNION, UNION ALL, and INTERSECT operators, SQL provides us with the MINUS operator that allows you to subtract one result set from another result set. The following illustrates the syntax of the MINUS operator. SELECT id FROM A MINUS SELECT id FROM B;

Hello. I have a set of tables and i want the result of the sum of Table1.Total - Sum Table2.Total but when i write my statement it gives me  When an EXCEPT operation is displayed by using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left anti semi join, and an INTERSECT operation appears as a left semi join. The following examples show using the INTERSECT and EXCEPT operators. The first query returns all values from the Production

Note that some database systems e.g., Microsoft SQL Server and PostgreSQL use the EXCEPT instead of MINUS . They have the same function. MySQL MINUS  Row 1 total = 500 (Qty) - 100 (Quantity) Row 2 total = 400 (Total from Row1) - 200 (Quantity) Row 3 total = 200 (Total from Row1) - 200 (Quantity) – midtonight Mar 7 '19 at 6:35. We need to look at your table's structures and sample data which must give the result you show. – Akina Mar 7 '19 at 6:36.

Comments
  • Do you have float columns? If yes check stackoverflow.com/questions/33322778/avg-of-float-inconsistency/…
  • This would seem to be a type issue. My guess is that decimals are being used for one of the columns.
  • What is the datatype of the CLEAVE_EARN_DAY and CLEAVE_DAY columns?
  • About sum(a)-sum(b) equality to sum(a-b) - only when there are no null values.
  • @Arvo Good point, of course when we are deal with nullable column we need to use ISNULL/COALESCE
  • kindly add explanation