# Sum With Division

I am attempting to do a calculation in SQL Server 2008 and I am getting incorrect results. Below is sample DDL and the query that I used, how should my statement be re-written in order to produce the accurate outcome?

My desired outcome is:

``````0.000029
0.000277142
0.003001501
0.000960527
0.004321608
``````

And this is DDL and query:

``````Declare @TableTest Table
(
fid int
,field1 int
,field2 int
,field3 int
,field4 int
)

INSERT INTO @TableTest (fid, field1, field2, field3, field4) Values
(1, 17, 12, 0,  1000000),
(2, 277,    0,  511,    1000000),
(3, 3000,   0,  500,    1000000),
(4, 900,    59, 1590,   1000000),
(5, 1300,   3000,   5000,   1000000)
``````

Which produces this outcome (incorrect)

``````fid (No column name)
1   0.0000000000
2   0.0000000000
3   0.0000000000
4   0.0000000000
5   0.0000000000
``````

EDIT
As suggested below it was a data type issue, I altered my columns from `int` to `decimal(10,2)` and am now getting my expected results - below is updated DDL

``````Declare @TableTest Table
(
fid int
,field1 decimal(10,2)
,field2 decimal(10,2)
,field3 decimal(10,2)
,field4 decimal(10,2)
)

INSERT INTO @TableTest (fid, field1, field2, field3, field4) Values
(1, '17.00',    '12.00',    NULL,   '1000000.00'),
(2, '277.00',   NULL,   '511.00',   '1000000.00'),
(3, '3000.00',  NULL,   '500.00',   '1000000.00'),
(4, '900.00',   '59.00',    '1590.00',  '1000000.00'),
(5, '1300.00',  '3000.00',  '5000.00',  '1000000.00')

Select
fid,
SUM((ISNULL(field1,0)+ISNULL(field2,0))/(NULLIF(ISNULL(field4,0)-ISNULL(field3,0),0)))
FROM @TableTest
GROUP BY fid
ORDER BY fid
``````

The calculation that I am using to get my desired output is:

``````(field1+field2)/(field4-field3)
``````

See screenshot, I was verifying my formula from SSMS by manually doing calculations in Excel and that is how I realized something was awry

• What is the query executed to provide the results you are getting?
– Nic
Commented Dec 18, 2016 at 3:54
• @Nic - I have edited my ? and added a screenshot, see if that provides enough information to allow yourself or someone else to be able to better assist. Commented Dec 18, 2016 at 6:02

You have left out some information needed to help you. All you have showed is the @TableTest and the insertion of data.

``````Declare @TableTest Table

( fid int ,field1 int ,field2 int ,field3 int ,field4 int );

INSERT INTO @TableTest (fid, field1, field2, field3, field4)
Values
(1, 17, 12, 0,  1000000),
(2, 277,    0,  511,    1000000),
(3, 3000,   0,  500,    1000000),
(4, 900,    59, 1590,   1000000),
(5, 1300,   3000,   5000,   1000000);

select * from @tabletest
``````

As you can see if you run the script, the contents of `Select @TableTest` that you inserted will be displayed.

Actually if you change fields `field1, field2, field3, field4` from `INT` to `DECIMAL` then the data will be of the proper data type for your calculation.

Here is my calculation:

Code:

``````select fid, (field1+field2)/ (field3+field4) from @TableTest
``````

Result:

``````fid (No column name)
1   0.0000290000000000000
2   0.0002768585252935749
3   0.0029985007496251874
4   0.0009574776105991473
5   0.0042786069651741293
``````

However, that is close, but not exact to your expected answer. Likely this is because of an error in datatypes.

Please provide enough information to be able to calculate correctly.

• thank you for the insight on datatype issue. I have edited my ? and added a screenshot, see if that provides enough information to allow yourself or someone else to be able to better assist. Commented Dec 18, 2016 at 6:02