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.

Fix:

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

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