Calculate group percentage to 2 decimal places - SQL
39,947
Solution 1
A better choice for conversion is the str()
function. (Documented here.)
In addition, you can do your calculation using window functions (assuming that you are using SQL Server 2005 or more recent). Here is my version of the query:
select hi.hostVersion,
str(((100.0*count(*)) / sum(count(*)) over ()), 5, 2)+'%'
from hostInfo hi
group by hi.hostVersion
Solution 2
Try something like this:
CAST(ROUND(100.0 * COUNT(*) / tot, 1) AS DECIMAL(10, 1))
Author by
solar411
Updated on June 19, 2020Comments
-
solar411 almost 4 years
I have the following query:
SELECT hostVersion, CONVERT(varchar, 100.0 * count(*) / tot,1) + '%' as 'Percent' FROM hostInfo, (SELECT COUNT(*) as tot FROM hostInfo) x GROUP BY hostVersion, tot
And receive the following output:
+--------------------------------+ | hostVersion | Percent | +--------------------------------+ | 5.0.0 | 26.666666666666% | +--------------------------------+ | 5.1.0 | 73.333333333333% | +--------------------------------+
How to I round to only 1 decimal place? (i.e. 26.7% & 73.3%)