9

I am using Postgresql to generate a simple quotient of two fields " a / b " = -3 / 300 = -.01 or -1.00%. Instead it displays as zero. I've tried many variations of to_char with no success. How could I get "select -3/300 as quotient" to display the quotient in xxx.xxx% format? Thanks

J-Ko
  • 2,503
  • 3
  • 9
  • 6
  • 6
    Can you elaborate? Because last time I checked `(3 / 300) == 0`, Postgres does integer division for input integers. Hence, `(a / b::float) * 100.0` might be a better idea. – dhke Jul 10 '17 at 17:04
  • Please elaborate by giving a more detailed example and describe exactly what step you took and what didn't work as expected. – herrbischoff Jul 10 '17 at 18:20
  • Sure, it's actually (-3 / 300) = 0.01 = 1.00%, and the ::float suggestion makes a difference! Thanks. – J-Ko Jul 10 '17 at 18:23

1 Answers1

21

To display with the percentage sign, you can use the following format. Don't forget to first multiply by 100.0 so 1) is it indeed a percentage and 2) as noted by @dhke the computation is done with floats, not integers. 

select to_char(100.0*-3/300,'999D99%') a;
    a
----------
   -1.00%
(1 row)
JGH
  • 15,928
  • 4
  • 31
  • 48
  • Thanks, JGH, this did yield positive changes. The "::float" suggestion above put me over the finish line but this technique for using no_char is one I will keep handy, it gives me more options. Thank you. – J-Ko Jul 10 '17 at 18:24
  • 9
    Great answer, I would also suggest using '990D99%' for formatting, to get 0.23% instead of .23% – Patrik Beck Sep 09 '19 at 08:41