Geek asks and answers

SQL Server: round and add percent sign

I have the following SQL query returning the result 92.967013425802, and I need to format it as 93%well and add a percent sign. I tried to change the amount to a round, but I got an error

The round function is not a valid window function and cannot be used with the OVER clause.

My request:

select 
    count(*) * 100.0 / sum(count(*)) over()
from 
    db_table_MetaData
group by 
    MetaValue
order by 
    MetaValue

Any help would be appreciated.

+3
source share
5 answers
select 
    --Cast(Round(count(*) * 100.0 / sum(count(*)), 0) as nvarchar(5) + '%' 
      CAST(Round(count(*) * 100.0 / sum(count(*)), 0) as nvarchar(5)) + '%'
from 
    db_table_MetaData

That should do the trick.

Basically you take function 08/15 ROUND()to get a numerical value. After that, you add the line to the line nvarchar(x)and add the line to your line. However, I have no way to test my syntax right now.

+1
source

, . , ?

.

select cast(Round('92.967013425802', 0) as nvarchar(10)) + '%'
+1

I don't know what the real problem is, but when I tried this request, it worked fine. Maybe this is placing brackets that went wrong with your request,

select 
MetaValue, round(count(*) * 100.0 / sum(count(*)) over(),0)
from db_table_MetaData
group by MetaValue
order by MetaValue

check its work on SQL Fiddle: http://www.sqlfiddle.com/#!3/55c82/3

0
source

Answer (count (*) * 100.0 / sum (count (*)) over (), 1)

0
source

I would use the FORMAT function with the 'p' parameter:

SELECT FORMAT(50.019/100.0,'p') as [Percentage]

This will give you a rounded result, for example:

Percentage
50.02 %

Check MSDN for more information:
https://msdn.microsoft.com/de-de/library/hh213505.aspx

0
source

More articles:


All Articles