Percentage in SQL Server

I want to find the percentage between two columns and I tried this :

select
(count(id)* 100.0 /(select count(id) FROM db.table  where log = '40')) as percentage
from db.table;

And the result is this:

418659.800405426477

Why the output number is like that? Also is there any way to make it look better and help end users to understand the percentage?

Answer

The output is like that because of the rules that SQL Server uses when doing arithmetic on numbers — the precision and scale are rather details (note that 1.0 is a numeric constant).

The explanation for this is in the documentation. Note that even I don’t bother trying to understand the rules for division.

I just want to point out that you can simplify the query:

select avg(case when log = '40' then 100.0 else 0 end) as percentage
from db.table;

This only scans the table once, so it should be faster. But it should produce a similar result.

If you want a given scale/precision then use round(), floor(), ceil(), str() or convert to a numeric.