Re: Query skips one set of records

[Miles Thompson]

Peter (and Felix),

Thank you, and I learned something today. Now I have a script that needs 
adjusting.
The amount certainly is "indeterminate".

The wonder is that we were able to run with this for so long.

Regards - Miles

At 03:57 PM 1/21/2007, Peter Brawley wrote:

>Miles
>
>Try it yourself with table tbl (id,cost) with rows (1,10),(1,50),(2,100):
>SELECT
>  id,
>  IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount
>FROM tbl
>GROUP BY id,cost;
>+------+--------+
>| id   | Amount |
>+------+--------+
>|    1 |    320 |
>|    2 |   1200 |
>+------+--------+
>SELECT
>  id,
>  IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount
>FROM tbl
>GROUP BY id,cost;
>+------+--------+
>| id   | Amount |
>+------+--------+
>|    1 |    220 |
>|    1 |    600 |
>|    2 |   1200 |
>+------+--------+
>
>Unless (i) there is exactly one unique value of cost per id, or (ii) the 
>query groups by cost as well as id, retrieved cost values are indeterminate.
>
>PB
>
>-----
>
>Miles Thompson wrote:
>>At 03:58 AM 1/19/2007, Felix Geerinckx wrote:
>>
>>>miles@stripped (Miles Thompson) wrote in
>>>news:6.1.1.1.0.20070118164903.03971d68@ style="color:#666">stripped:
>>>
>>>
>>> > This query:
>>> >
>>> > SELECT
>>> >      member_id,
>>> >      member_sub_id,
>>> >      IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 )
>>> >      ), ( SUM(
>>> > monthly_cost * 12.00 ) ) ) AS Amount
>>> > FROM subinfo
>>> > WHERE
>>> >      MONTH(anniv_bill_date) = 12 AND
>>> >      MONTH(fetch_date) = 12 AND
>>> >      YEAR(fetch_date) = 2006 AND
>>> >      pay_method = 'Invoice'
>>> >      GROUP BY member_id
>>> >
>>> > Should return Amount as $280 : ( 10*2 ) + 200 for the first record
>>> > plus 5 * 12 for the next one. Instead it is returning $180.
>>>
>>>
>>>The monthly_cost column is indeterminate (read meaningless) outside of an
>>>aggregate function, since you're not grouping on it.
>>>See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html
>>>
>>>--
>>>felix
>>
>>Felix,
>>
>>Sorry, but I do not understand what you are trying to explain. I have 
>>looked at that page in the manual, but I'm no clearer on its meaning.
>>
>>What I understand my SQL statement to say, is for every member_id add up 
>>the monthly cost, subject to this condition: If the monthly cost = 10 
>>then calculate  monthly_cost *2 plus 200 and add it to the total for that 
>>member_id, otherwise calculate monthly_cost * 12 and add that to the 
>>total for the member_id.
>>
>>How does this differ from the second example given in the previous 
>>section, http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ?
>>
>>What would the point be on grouping on monthly_cost? Apart from that, it 
>>works just fine for all other member_id's.
>>
>>If you could bring a little more light I would appreciate it.
>>
>>Regards - Miles
>
>
>--
>No virus found in this outgoing message.
>Checked by AVG Free Edition.
>Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007