4

I have this table structure on a SQL Server 2008 R2 database:

  CREATE TABLE FormTest
(   
clientid char(10),
DateSelected date,
A int,
B int,
C int
)

I filled the table FormTest with the following information

clientid        DateSelected      A       B     C
x1              2006-06-09     65150    4921    1
x2              2006-05-05     155926   69092   1
x3              2006-01-20     95603    156892  1
x4              2006-01-20     30704    164741  1
x4              2006-02-03     65150    174834  1
x5              2006-04-28     59629    4921    1
x6              2006-01-27     30704    162356  1
x7              2006-06-30     65150    4921    1
x8              2006-07-10     65150    4921    1

And finally, I run this sql query:

SELECT clientid, (((a+ b + c) / 3) / 216647 * 10) AS Formula1 
    From FormTest

But then I got these results:

clientid        Formula1      
x1              0
x2              0
x3              0
x4              0
x4              0
x5              0
x6              0
x7              0
x8              0

Can anybody tell me what am I doing wrong?

webyacusa
  • 524
  • 2
  • 9
  • 28
  • SELECT (((CAST(65150 AS FLOAT) + CAST(4921 AS FLOAT) + CAST(1 AS FLOAT)) / 3) / 216647 * 10) AS Formula1, although you could probably just cast the result of your a + b + c as float and get the same result. Have a bash and see how you go. – Mr Moose Jun 20 '12 at 03:41

4 Answers4

5

It's because you are doing integer division. You should convert one of the operands to float, or decimal (depending on the precision and purpose of the calculation you are doing), using something like:

((CAST((a+ b + c) AS FLOAT) / 3) / 216647 * 10)

or possibly:

(((a+ b + c) / 3.0) / 216647.0 * 10)
Pablo Romeo
  • 11,298
  • 2
  • 30
  • 58
2

You're performing integer arithmetic, so your results will always be rounded down to the nearest whole number. Since you're dividing by 3, then by 216647, based on the numeric inputs your result is getting rounded down to 0 every time. You'll need to use either a decimal (exact) or floating point (approximate) data type and/or casting if you want to get non-integer results.

Joel C
  • 5,547
  • 1
  • 21
  • 31
0
declare @a int
declare @b int
declare @c int
set @a=65150
set @b=4921
set @c=1

select convert(float,((@a+@b+@c)/ 216647.0 * 10) )
praveen
  • 12,083
  • 1
  • 41
  • 49
0

Dividing integers will result in an integer (The remainder part will be ignored). Hence, when dividing, work with decimals or floats. This should work in addition to the float solutions given earlier. Replace (12,2) with your preferred precision:

SELECT clientid, (((
Cast(a as decimal(12,2)) + 
Cast(b as decimal(12,2)) + 
Cast(c as decimal(12,2)) 
) / 3) / 216647 * 10) AS Formula1 
FROM FormTest
Isaac Njiru
  • 31
  • 4