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:
[EMAIL PROTECTED] (Miles Thompson) wrote in
news:[EMAIL PROTECTED]:
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
