List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 2 1999 12:59am
Subject:Subtraction vapor
View as plain text  
>>>>> "Para-dox" == Para-dox  <paradox@stripped> writes:

Para-dox> Something seems to be causing inconsistancy...
Para-dox> SELECT (Sum(PAYMENTS.AMT)/100) FROM INVOICES LEFT JOIN PAYMENTS ON
Para-dox> PAYMENTS.INVNUM = INVOICES.INVNUM WHERE INVOICES.INVNUM = 133674 GROUP BY
Para-dox> INVOICES.INVNUM;

Para-dox> This returns one row with 134.56



Para-dox> SELECT (((((10000+INVOICES.TAXPERC+IF(INVOICES.HNDTYPE=0, INVOICES.HNDVAL,
Para-dox> 0))*INVOICES.SUBTOTAL/10000)+IF(INVOICES.HNDTYPE<>0, INVOICES.HNDVAL,
Para-dox> 0)))/100) FROM INVOICES LEFT JOIN PAYMENTS ON PAYMENTS.INVNUM =
Para-dox> INVOICES.INVNUM WHERE INVOICES.INVNUM = 133674 GROUP BY INVOICES.INVNUM;

Para-dox> This returns one row with 134.56


Para-dox> SELECT (((((10000+INVOICES.TAXPERC+IF(INVOICES.HNDTYPE=0, INVOICES.HNDVAL,
Para-dox> 0))*INVOICES.SUBTOTAL/10000)+IF(INVOICES.HNDTYPE<>0, INVOICES.HNDVAL,
Para-dox> 0)))/100) - (Sum(PAYMENTS.AMT)/100) FROM INVOICES LEFT JOIN PAYMENTS ON
Para-dox> PAYMENTS.INVNUM = INVOICES.INVNUM WHERE INVOICES.INVNUM = 133674 GROUP BY
Para-dox> INVOICES.INVNUM;

Para-dox> This *should* return 0, I believe, but instead it returns -134.56  ??
Para-dox> Am I forgetting something?

Hi!

No, it doesn't need to be 0.  As the column:

(((((10000+INVOICES.TAXPERC+IF(INVOICES.HNDTYPE=0, INVOICES.HNDVAL,
0))*INVOICES.SUBTOTAL/10000)+IF(INVOICES.HNDTYPE<>0,INVOICES.HNDVAL,0)))/100)

Uses values that is NOT in the GROUP BY part, this may return
information from any matching rows in the set.  The row may be
different depending off how MySQL optimizes the query.  Generally you
should NEVER reference to a column outside a summary function if the
column is not in the GROUP BY part if you are not 100 % sure that this 
will contain the same value for all found rows.
(You can find more about this in the MySQL manual)

Fix:

Use instead:

SUM(((((10000+INVOICES.TAXPERC+IF(INVOICES.HNDTYPE=0, INVOICES.HNDVAL,
0))*INVOICES.SUBTOTAL/10000)+IF(INVOICES.HNDTYPE<>0,INVOICES.HNDVAL,0)))/100)) 
-(Sum(PAYMENTS.AMT)/100)

Regards,
Monty

Thread
Subtraction vaporPara-dox24 Jul
  • Re: Subtraction vaporChristian Mack27 Jul
  • Re: Subtraction vaporPara-dox27 Jul
  • Re: Subtraction vaporChristian Mack30 Jul
  • Subtraction vaporMichael Widenius2 Aug