Обсуждение: problem in sql - sum()
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
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
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
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
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