Maths Issue in SQL Server

Question

I am trying to perform a maths equation in SQL to calculate service up time. I have a column with pass counts and a column with fail counts.

I have achieved this sum in VB for the application side and the sum is as follows: pass / (pass+fail) * 100.

The results should be on some rows 99.98 but I can't get SQL to give me the same result. I either get 100 or 0 as the result.

This is my SQL query:

select pass / sum(pass+fail) * 100 as total, friendlyname from sensors group by pass, friendlyname

Example row:

Friendly Name  | Pass  | Fail
_____________________________

Cloudflare     | 25527 | 23

So as you can see the result should be 99.90 when done on a calculator using above formula but SQL reports this sum as 0.

Really would be grateful for some help!

Thanks for your reply. I just tried the above but it is now showing as 0.0 and 100.0 but still not correct. Thanks — David Wakely, Nov 28 '19 at 08:49

score 2 · Answer 1 · answered Nov 28 '19 at 08:48

Make at least one portion of the division a floating point number, to force that precision. Also, your math seems to be off. If you want to compute the ratio of passes/fails to the total counts, then sums should appear in both the numerator and denominator.

select
    100.0 * sum(pass) / sum(pass+fail) as pass_pct,
    100.0 * sum(fail) / sum(pass+fail) as fail_pct
    friendlyname
from sensors
group by
    friendlyname;

Amira Bedhiafi · Accepted Answer · 2019-11-28T08:56:16.050

0

Try this :

select
    (pass * 100.0) / sum(pass+fail) as total,
    friendlyname
from sensors
group by friendlyname, pass

edited Nov 28 '19 at 08:56

answered Nov 28 '19 at 08:49

Amira Bedhiafi

8,088
6
24
60

Just tried this and it works! Many many thanks, this has been driving me mad for days! – David Wakely Nov 28 '19 at 08:51
This _can't_ possibly work, because `pass` does not appear in the `GROUP BY` clause. Please edit your question and show sample data. – Tim Biegeleisen Nov 28 '19 at 08:54
Yeah I had to add pass in when I run it. No big deal though! – David Wakely Nov 28 '19 at 09:01
It makes no sense for `pass` to appear as a group by column and also inside the call to sum. – Tim Biegeleisen Nov 28 '19 at 09:03

Maths Issue in SQL Server

2 Answers2