CODEWITHSUNDEEP
sqlsql-server
data addicted
data addicted

Reputation: 69

Addition and Division

I needed help with doing division calculations with one decimal. I am trying to sum and divide the result. My Y and X are integers.

SELECT a.YearMonth,
       (CONVERT(DECIMAL(9,1),Y_Service)+(CONVERT(DECIMAL(9,1),x_Service)/2)) AS Average_Of_Period
FROM table_IB a
  INNER JOIN table_UB b ON a.YearMonth=b.YearMonth

This is the result I get:

YearMonth|  Average_Of_Period         
  2015-03    276318.500000

The correct answer is :185532,5

My Y and X values differ from 4 digits to 6 digits

Upvotes: 1

Views: 70

Answers (3)

APH
APH

Reputation: 4154

Use this instead; it's easier:

select (x + y)/2.0

Adding the decimal point to the 2 will change the result from integer division to standard division. This will also work, for the same reason:

select (x + y)*1.0/2

If you want to explicitly convert decimals, easiest to add first - as other commenters have mentioned this will make it easier to spot mathematical errors:

select cast(x + y as decimal(10,2))/2

Upvotes: 0

roman
roman

Reputation: 117485

Readability matters. In case you have to do some ugly casts before calculation, you can always do it with common table expression or outer apply:

select
    a.YearMonth,
    (calc.Y_Service + calc.x_Service) / 2 as Average_Of_Period
from table_IB as a
    inner join table_UB as b on a.YearMonth = b.YearMonth 
    outer apply (select
       CONVERT(DECIMAL(9,1), Y_Service) as Y_Service,
       CONVERT(DECIMAL(9,1), x_Service) as x_Service
    ) as calc

And then you'd not miss that you're doing Y_Service + x_Service / 2 instead of (Y_Service + x_Service) / 2

Upvotes: 0

Mike B.
Mike B.

Reputation: 1502

Looks like your operation is (y) + (x / 2)?

Should that be ( y + x ) / 2?

Upvotes: 3

Related Questions