Long arithmetic with SQL SELECT statement

Question

The basic idea is I have 3 separate SELECT statements that output a number. I need a percentage from those numbers. So the problem would look like this:

(a.count_one + b.count_two) -
(b.count_two + c.count_three) / (a.count_one + b.Count_two) * 100

When I do the code found below I only get the output of the first (a.count_one + b.count_two). BUT if I comment out the third portion which is / (a.count_one + b.count_two) I successfully get the correct solution from (a.count_one + b.count_two) – (b.count_two + c.count_three).

So it appears, doing math of two statements is great, but when I throw in a third statement it pukes and only wants to show the solution of the first problem (a.count_one + b.count_two), but does not even try to calculate the solution for (a.count_one + b.count_two) – (b.count_two + c.count_three) anymore. I'm a bit stumped why.

Select
    (a.Count_one + b.Count_two) - 
    (b.Count_two + c.Count_three) / (a.Count_one + b.Count_two) * 100 
    as 'Final_Percentage' 
from 

(
select COUNT(v_Summary.ResourceID) AS Count_one
From [DB1].[dbo].[v_Summary]
) a,

(
select count([CN]) as Count_two
From [DB2].[dbo].[Computers] 
WHERE cn NOT IN (SELECT name0 FROM [DB1].[dbo].[v_system] where v_system.Client0 = '1')
) b,

(
select COUNT(v_Summary.ResourceID) AS Count_three
From [DB1].[dbo].[v_Summary]
Where Description like '%/Fail'
) c

And to give additional information. The math problem with numbers:

(54558 + 373) – (373 + 117) / (54558 + 373) * 100

Or further solved:

(54931) - (490) / 55304 * 100 = 98.44%

I added SQL Server tag based on the use of `[]` and `dbo` in the code. — Vladimir Baranov, Aug 31 '17 at 04:18

Vladimir Baranov · Accepted Answer · 2017-08-31T04:17:43.203

COUNT function returns int. When you divide two int values the result is again int in SQL Server. So, 490 / 55304 is 0. Then 0 * 100 is 0 again.

Cast the values to a suitable type, like float.

By the way,

(54931) - (490) / 55304 * 100

is not equal to ~98.44, it is equal to ~54930.11399 You need parentheses to get the result you expect:

((54931) - (490)) / 55304 * 100

is equal to ~98.43953421

So, the final query should look like this:

Select
    ((a.Count_one + b.Count_two) - (b.Count_two + c.Count_three)) 
    / (a.Count_one + b.Count_two) * 100 
    as 'Final_Percentage' 
from 

(
select CAST(COUNT(v_Summary.ResourceID) AS float) AS Count_one
From [DB1].[dbo].[v_Summary]
) a,


(
select CAST(count([CN]) AS float) as Count_two
From [DB2].[dbo].[Computers] 
WHERE cn NOT IN (SELECT name0 FROM [DB1].[dbo].[v_system] where v_system.Client0 = '1')
) b,


(
select CAST(COUNT(v_Summary.ResourceID) AS float) AS Count_three
From [DB1].[dbo].[v_Summary]
Where Description like '%/Fail'
) c

you will also need parenthesis around this expression (a.Count_one + b.Count_two) - (b.Count_two + c.Count_three) otherwise you will attempt to divide (b.Count_two + c.Count_three) by (a.Count_one + b.Count_two) which is not what you want — 31 bit, Aug 31 '17 at 04:22
Thank you Vladimir, this was perfect. And sorry about the bad math in my question. — CDeCarlo, Aug 31 '17 at 07:26

Carsten Massmann · Answer 2 · 2017-08-31T04:38:57.573

0

You should also get the right result by saying

Select
100.*(a.Count_one - c.Count_three) / (a.Count_one + b.Count_two)
as 'Final_Percentage' 
from ... -- unchanged subqueries, as supplied in your question

By moving the *100. right to the front you implicitly convert the first product to float and the type then stays that way for the rest of the calculation. Also, you might have noticed, I simplified the formula, since b.Count_two gets subtracted from itself, so you can leave it out altogether. I have not tested this but I am pretty sure it will work.

edited Aug 31 '17 at 04:38

answered Aug 31 '17 at 04:33

Carsten Massmann

26,510
2
22
43

Strictly speaking, `100.0` is not `float`, it is `decimal` in SQL Server. It may be OK here, but it also may have its quirks. – Vladimir Baranov Aug 31 '17 at 04:52
@Vladimir: thanks for pointing that out! Decimals provide 38 digits to represent numbers and should therefore be adequate for this type of calculation, but you are right, they might run into problems when dealing with *really* big numbers (astronomical calculations). For those cases the float type will be better, also see here https://stackoverflow.com/a/7158770/2610061 – Carsten Massmann Aug 31 '17 at 05:25
Thank you for the help Cars10m but unfortunately I was left with the same result as before with the first problems solution (in your example a.count_one - c.count_three) but now times 100 since the multiplication moved to front. Strange. – CDeCarlo Aug 31 '17 at 07:28

Long arithmetic with SQL SELECT statement

2 Answers2