Arbitrary Decimal Truncation with PostgreSQL

Question

Arbitrary Decimal Truncation with PostgreSQL

Navigation

#1 by (4 votes)

2

I am doing an SQL query using the sum() function. This function adds integers and fractional numbers. The problem is that it returns a double precision with a very large number. How do I limit to two decimal places after the point?

Code

SELECT sum(comprimento * largura * qtde) as met FROM seccionada WHERE cod_secc = 'SE1'

Query result:

40.1699985265732

I wanted the answer to be:

40.16

sql postgresql

asked by anonymous 30.10.2016 / 18:54

1 answer

It is possible to get data from the entire month in postgreSQL How to restrict the filling of only numbers in an input with AngularJs?

score 4 · Accepted Answer

For this there is this function:

trunc( valor, [ casas_decimais ] )

and this:

round( valor, [ casas_decimais ] )

The difference of the two is that trunc does not round, returning 40.16 as requested in your question, and round returns 40.17 .

Applied to your code:

SELECT
   trunc( sum(comprimento * largura * qtde), 2 ) as met
FROM
   seccionada
WHERE
   cod_secc = 'SE1'

See both in action in SQL Fiddle .

SQL