Want only two decimal value in percentage

Question

I'm using below query to generate the percentage in a new column but I want only two decimal places.

    SELECT 
count( *) TestCount
,count(case when result_status = 'Not A Finding' then 1 end) TestPassedCount
,count(case when result_status = 'Not A Finding' then 1 end) / count(*) ThePercentage
,asset 
 FROM Table_Name
 group by asset

Current Output:

             TestCount  TestPassedCount    Percentage   Asset
                 18             7          0.388888889  DB2MOTIV:DBMOTIVA@mxemch010405ads.mx.hsbc

Required Output:

             TestCount  TestPassedCount    Percentage   Asset
                 18             7              38.89    DB2MOTIV:DBMOTIVA@mxemch010405ads.mx.hsbc

Regards,

Bharath Vikas

You can't possibly be getting 0.38888889 from the code posted. You have count / count which is two integers so you would get an integer result. — Sean Lange, Nov 02 '17 at 15:10

dns_nx · Answer 1 · 2017-11-02T15:15:03.503

0

Assuming that is what you want to achieve, just multiply your result with 100 and add round function:

SELECT 
 count( *) TestCount
 ,count(case when result_status = 'Not A Finding' then 1 end) TestPassedCount
 ,round((count(case when result_status = 'Not A Finding' then 1 end) / count(*)) * 100,2) ThePercentage
 ,asset 
FROM 
 Table_Name
group by 
 asset

edited Nov 02 '17 at 15:15

answered Nov 02 '17 at 15:09

dns_nx

3,651
4
37
66

score 0 · Answer 2 · answered Nov 02 '17 at 15:09

Use ROUND method

 SELECT 
        count( *) TestCount
        ,count(case when result_status = 'Not A Finding' then 1 end) TestPassedCount
        ,ROUND((count(case when result_status = 'Not A Finding' then 1 end) / count(*))*100,2) ThePercentage
        ,asset 
         FROM Table_Name
         group by asset

score 0 · Accepted Answer · answered Nov 02 '17 at 15:09

0

Try this:

 Round(  count(case when result_status = 'Not A Finding' then 1 end) / count(*) * 100 , 2) ThePercentage

answered Nov 02 '17 at 15:09

Michał Turczyn

32,028
14
47
69

Want only two decimal value in percentage

3 Answers3