|
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 |
