Rounding problems - Mailing list pgsql-general

From Paolo Saudin
Subject Rounding problems
Date
Msg-id 002801c9cbeb$f6b59c20$e420d460$@it
Whole thread Raw
Responses Re: Rounding problems
Re: Rounding problems
List pgsql-general

Hi,

 

I have a problem with a query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average with one decimals, should give 1.4.

The first query with  -  cast( tables_seb.tbl_arvier_chamencon.id_1  AS numeric) AS value  - give the expected result, while the second one with - tables_seb.tbl_arvier_chamencon.id_1 AS value - give 1.3.

 

Which could be the reason ??

 

 

-- data

fulldate timestamp;        tables_seb.tbl_arvier_chamencon.id_1- reals

2009-03-29 00:00:00;       1.3

2009-03-29 00:30:00;       1.4

 

--Good query

SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric), 1 ) AS value

FROM

(

                SELECT _master_30.fulldate AS data,

                cast( tables_seb.tbl_arvier_chamencon.id_1  AS numeric) AS value

                --tables_seb.tbl_arvier_chamencon.id_1 AS value

                FROM _master_30

                LEFT JOIN tables_seb.tbl_arvier_chamencon ON _master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate

                WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND '2009-03-29 00:59:59'

                ORDER BY data

) foo

GROUP BY 1 ORDER BY 1;

-- value = 1.4 OK

 

 

--wrong query

SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric), 1 ) AS value

FROM

(

                SELECT _master_30.fulldate AS data,

                --cast( tables_seb.tbl_arvier_chamencon.id_1  AS numeric) AS value

                tables_seb.tbl_arvier_chamencon.id_1 AS value

                FROM _master_30

                LEFT JOIN tables_seb.tbl_arvier_chamencon ON _master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate

                WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND '2009-03-29 00:59:59'

                ORDER BY data

) foo

GROUP BY 1 ORDER BY 1

-- value = 1.3 NOT OK

 

-- test

select round(cast( (1.3 + 1.4)::real / 2 as numeric), 1);

-- value = 1.4 OK

 

 

Using PostgreSQL 8.3.7 on Windows Server 2008

 

 

Thank in advance,

Paolo Saudin

 

pgsql-general by date:

Previous
From: garry@schoolteachers.co.uk
Date:
Subject: windows service
Next
From: Thomas Kellerer
Date:
Subject: Re: windows service