Calculate group percentage up 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 get the following output:

 +--------------------------------+ | hostVersion | Percent | +--------------------------------+ | 5.0.0 | 26.666666666666% | +--------------------------------+ | 5.1.0 | 73.333333333333% | +--------------------------------+ 

How do I round to 1 decimal point? (i.e. 26.7% and 73.3%).

Answer 1

The best choice to convert is the str() function. (Documented here .)

In addition, you can perform calculations using window functions (assuming you are using SQL Server 2005 or later). Here is my version of the request:

 select hi.hostVersion, str(((100.0*count(*)) / sum(count(*)) over ()), 5, 2)+'%' from hostInfo hi group by hi.hostVersion 

Answer 2

Try something like this:

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

Answer 3

 SELECT hostVersion, CONVERT(varchar, ROUND(100.0 * count(*) / tot,2),1) + '%' as 'Percent' FROM hostInfo, (SELECT COUNT(*) as tot FROM hostInfo) x GROUP BY hostVersion, tot