postgresql Remove trailing zeroes

Question

I have a query which is proving to be cumbersome for me. I want to remove the trailing zeroes from the result.

Remove trailing zeros from decimal in SQL Server

 select concat(100 * round(cast(count(ft.*) filter (where "Realtor_Sale" = 'Yes') 
as numeric(12,5)) / 
cast(count(ft.*) as numeric(12,5)),3),'%') as "Realtor Sales"

Result I am getting is:

84.800% --------------> I want 84.8%.

I tried doing this as well:

select concat(100 * round(cast(cast(count(ft.*) filter (where "Realtor_Sale" = 'Yes') 
as decimal(18,5)) as float) / 
cast(cast(count(ft.*) as decimal(18,5)) as float),3), '%') as "Realtor Sales"

Error:

ERROR:  function round(double precision, integer) does not exist
select round(cast(cast(count(ft.*) filter (where "Realtor_Sa...

How do I get the result to round of to 84.8%?

Try `trim(trailing '0' from cast(ft as varchar(20) ))` – jarlh Mar 10 '17 at 14:56 — jarlh, Mar 10 '17 at 14:56

score 4 · Answer 1 · answered Jul 10 '20 at 19:25

With PostgreSQL 13 it is matter of calling trim_scale function:

trim_scale ( numeric ) → numeric

Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes

trim_scale(8.4100) → 8.41

select trim_scale(100.0 * count(ft.*) filter (where "Realtor_Sale" = 'Yes')/ 
                  count(ft.*) ) ||'%'as "Realtor Sales"

db<>fiddle demo

a_horse_with_no_name · Accepted Answer · 2017-03-10T15:02:02.457

3

No need for the many casts, just use to_char() on the result:

select to_char((100 * count(ft.*) filter (where "Realtor_Sale" = 'Yes'))::decimal 
                / count(ft.*), 'FM99.0%') as "Realtor Sales"

edited Mar 10 '17 at 15:02

answered Mar 10 '17 at 15:00

a_horse_with_no_name

497,550
91
775
843

1

Do you need `::decimal` ?? Im wondering would `100.0 * count(ft.*)` give you same result? I mean for precision and all that. – Juan Carlos Oropeza Mar 10 '17 at 15:03
@JuanCarlosOropeza: yes you need that because `count()` returns an integer and then the whole result would be an integer, so you would lose all decimals – a_horse_with_no_name Mar 10 '17 at 15:23
I usually use `100.0` to convert the integer. The question is what type you get after `100.0 * integer`. Because float can give you some arithmetic errors. – Juan Carlos Oropeza Mar 10 '17 at 15:30
@JuanCarlosOropeza: that is another possibility, yes – a_horse_with_no_name Mar 10 '17 at 15:32

score 0 · Answer 3 · answered Apr 01 '22 at 05:38

The problem with the ROUND function is, it also adds 0 at the end for the integer values.

E.g. select round(238,2) --> 238.00

Here is the solution i tried, which also preserves the integer data.

select cast(trim(trailing '0' from round(238.0100::numeric,2)::text)::numeric as text)

This will round the values with removing the trailing spaces as well as keeps the whole number as it is.

postgresql Remove trailing zeroes

3 Answers3

Linked