7

I have a number want to format it as percentage in Presto. I want to format 0.18932 as 18.93%.

I tried:

format('%s%%', decimal_number*100)
format('%s%%', round((a.decimal_number*100), 2))

Here is the result: none formatted: 0.019435618 first method: 1.9435618000% 2nd method: 1.9400000000%

Any easier way to format it as 1.94%

Martin Traverso
  • 4,731
  • 15
  • 24
Jason LiLy
  • 634
  • 2
  • 9
  • 19

3 Answers3

6

The format function accepts any of the Java format string specifiers:

presto> select format('%.2f%%', 0.18932 * 100);
 _col0
--------
 18.93%
(1 row)
Martin Traverso
  • 4,731
  • 15
  • 24
  • Thanks. I got the same result but with a more complicated way. format('%s%%', format('%,.2f',0.18932 * 100)) – Jason LiLy Jul 06 '20 at 21:13
  • 2
    Seems like "format" function is not available in "my" presto. There has to be another way – Jonny Nov 10 '21 at 04:08
1

For those of you who, like me, came here looking to round a decimal or double field down to n decimal places but didn't care about the % sign, you have a few other options than Martin's answer.

If you want the output to be type double then truncate(x,n) will do this. However, it requires x to be type decimal.

select truncate( cast( 1.23456789 as decimal(10,2)) , 2); -> 1.23 <DOUBLE>

If you feel that the truncate is kind of useless here since the decimal(10,2) is really doing the work, then you're not alone. An arguably equivalent transformation would be.

select cast( cast( 1.23456789 as decimal(10,2)) as double); -> 1.23 <DOUBLE>

Is this syntactically better or more performant than truncate(), I have no idea, I guess you get to choose.

And if you don't give a hoot about what type results from the transformation, then I suppose the below is the most direct method.

cast( 1.23456789 as decimal(10,2);

Austin A
  • 2,990
  • 6
  • 27
  • 42
0

For me it worked like this using format() and round() combined:

select format('%s%%',round(count(*) * 100.00 / sum(count(*)) over(), 2)) as percent
Maria Clara Bezerra
  • 509
  • 3
  • 5