Calculate group percentage to 2 decimal places - SQL

Question

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%)

Answer 1

Try something like this:

CAST(ROUND(100.0 * COUNT(*) / tot, 1) AS DECIMAL(10, 1))

Answer 2

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