Thread: problem in sql - sum()

problem in sql - sum()

From
ivan@adicional.com.br
Date:
Hi. I have a problem identified.
In select :

select data_carteira, sum(valor_carteira) from carteira where
data_carteira between '2016-01-01' and '2016-02-18'
GROUP BY data_carteira order by data_carteira

return a values from month. In the day 18, return the value 82.915.213,14.

Now, if I use the select :
select data_carteira, sum(valor_carteira) from carteira where
data_carteira=3D'2016-01-18'
GROUP BY data_carteira order by data_carteira

return 103.629.305,96.

It's very diferenty.
Only change is date.

The version in use is :
PostgreSQL 9.0.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.6.=
3 20120306 (Red Hat 4.6.3-2), 64-bit

Please, helpe me.

Thanks,

Ivan Rodrigues
Brazil - S=C3=A3o Paulo

Re: problem in sql - sum()

From
"David G. Johnston"
Date:
On Tue, Mar 22, 2016 at 7:42 AM, <ivan@adicional.com.br> wrote:

> Hi. I have a problem identified.
> In select :
>
> select data_carteira, sum(valor_carteira) from carteira where
> data_carteira between '2016-01-01' and '2016-02-18'
> GROUP BY data_carteira order by data_carteira
>
> return a values from month. In the day 18, return the value 82.915.213,14=
.
>
>
=E2=80=8B=E2=80=8BWhich day 18?=E2=80=8B



> Now, if I use the select :
> select data_carteira, sum(valor_carteira) from carteira where
> data_carteira=3D'2016-01-18'
> GROUP BY data_carteira order by data_carteira
>
> return 103.629.305,96.
>
> It's very diferenty.
> Only change is date.
>
> The version in use is :
> PostgreSQL 9.0.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit
>

You are going to find little help here given you are running an ancient
and, even when within support, woefully unpatched release of the software.

=E2=80=8BDavid J.=E2=80=8B

Re: problem in sql - sum()

From
Alvaro Herrera
Date:
ivan@adicional.com.br wrote:
> Hi. I have a problem identified.
> In select :
>
> select data_carteira, sum(valor_carteira) from carteira where
> data_carteira between '2016-01-01' and '2016-02-18'
> GROUP BY data_carteira order by data_carteira
>
> return a values from month. In the day 18, return the value 82.915.213,14.

This query means "the sum of all values for all the days between Jan 1st
and Feb 18th".

> Now, if I use the select :
> select data_carteira, sum(valor_carteira) from carteira where
> data_carteira='2016-01-18'
> GROUP BY data_carteira order by data_carteira

"The sum of all values for Jan 18th".

> return 103.629.305,96.
>
> It's very diferenty.

I don't understand why you think this is a problem.  The values in that
table could be anything --- What this says is that there are rows for
the 2016-01-18 date that add up exactly to 103.629.305,96; and the
values for the other dates add to -20.714.093,82.

> Only change is date.

Right.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: problem in sql - sum()

From
"David G. Johnston"
Date:
On Tue, Mar 22, 2016 at 1:25 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> ivan@adicional.com.br wrote:
> > Hi. I have a problem identified.
> > In select :
> >
> > select data_carteira, sum(valor_carteira) from carteira where
> > data_carteira between '2016-01-01' and '2016-02-18'
> > GROUP BY data_carteira order by data_carteira
> >
> > return a values from month. In the day 18, return the value
> 82.915.213,14.
>
> This query means "the sum of all values for all the days between Jan 1st
> and Feb 18th".
>

=E2=80=8BHuh?=E2=80=8B

=E2=80=8BThis query returns 49 (ish) rows, one for each date, and should in=
clude a
value for January 18th.  It should be possible to compare the value of the
January 18 row in this query with the single row returned by the following
query - and they should, in theory, match.

The OP claims that the corresponding rows don't match but I personally have
not excluded user error - specifically that my assumption (above) of what
is being compared matches that which is actually being compared.

I'm also not that inclined to debug 9.0.8 when the entire 9.0 release is
out of support and there are 13 point releases - any of which might have
been required to fix a bug that exhibits this behavior.


> > Now, if I use the select :
> > select data_carteira, sum(valor_carteira) from carteira where
> > data_carteira=3D'2016-01-18'
> > GROUP BY data_carteira order by data_carteira
>
> "The sum of all values for Jan 18th".
>
> > return 103.629.305,96.
> >
> > It's very diferenty.
>
> I don't understand why you think this is a problem.  The values in that
> table could be anything --- What this says is that there are rows for
> the 2016-01-18 date that add up exactly to 103.629.305,96; and the
> values for the other dates add to -20.714.093,82.
>
> > Only change is date.
>
> Right.
>
>
=E2=80=8BDavid J.
=E2=80=8B

Re: problem in sql - sum()

From
Alvaro Herrera
Date:
David G. Johnston wrote:

> The OP claims that the corresponding rows don't match but I personally have
> not excluded user error - specifically that my assumption (above) of what
> is being compared matches that which is actually being compared.
>
> I'm also not that inclined to debug 9.0.8 when the entire 9.0 release is
> out of support and there are 13 point releases - any of which might have
> been required to fix a bug that exhibits this behavior.

I misspoke.  My intention here was to point out that this is most likely
user error, not a software bug, despite how obsolete the version.  Yes,
he needs to upgrade, but I cannot honestly believe that upgrading would
"solve" this particular problem.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services