From: Peter Brawley
Date: January 21 2007 7:57pm
Subject: Re: Query skips one set of records
List-Archive: http://lists.mysql.com/mysql/204541
Message-Id: <45B3C5A8.1080002@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@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