# What is wrong with this SQL Server query division calculation?

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 Cx1 2006-06-09 65150 4921 1x2 2006-05-05 155926 69092 1x3 2006-01-20 95603 156892 1x4 2006-01-20 30704 164741 1x4 2006-02-03 65150 174834 1x5 2006-04-28 59629 4921 1x6 2006-01-27 30704 162356 1x7 2006-06-30 65150 4921 1x8 2006-07-10 65150 4921 1``

And finally, I run this sql query:

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

But then I got these results:

``clientid Formula1x1 0x2 0x3 0x4 0x4 0x5 0x6 0x7 0x8 0``

Can anybody tell me what am I doing wrong?

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

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.

``````declare @a int
declare @b int
declare @c int
set @a=65150
set @b=4921
set @c=1

select convert(float,((@[email protected][email protected])/ 216647.0 * 10) )
``````