Sum With Division

Question

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

Answer 1

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.