Re: Calculating a value based on an aliased column

[Eamon Daly]

Okay, upon reading the documentation, I came away with the
same conclusion Shawn did. That said, I was curious to see
what the results would look like, and lo and behold I got
exactly the right result, even with GROUP BY:
mysql> SELECT
   -> id,
   -> name,
   -> @p1 := CASE
   ->  WHEN in_method = 'Add' THEN value + in_value
   ->  WHEN in_method = 'Subtract' THEN value - in_value
   ->  WHEN in_method = 'Multiply' THEN value * in_value
   ->  WHEN in_method = 'Divide' THEN value / in_value
   -> END as phase_1,
   -> CASE
   ->  WHEN out_method = 'Add' THEN @p1 + out_value
   ->  WHEN out_method = 'Subtract' THEN @p1 - out_value
   ->  WHEN out_method = 'Multiply' THEN @p1 * out_value
   ->  WHEN out_method = 'Divide' THEN @p1 / out_value
   -> END as phase_2
   -> FROM filter
   -> ;
+----+------+---------+---------+
| id | name | phase_1 | phase_2 |
+----+------+---------+---------+
|  1 | foo  |       1 |       3 |
|  2 | foo  |       0 |      -4 |
|  3 | foo  |       8 |      48 |
|  4 | bar  |      -3 |  -0.375 |
|  5 | bar  |      45 |      55 |
+----+------+---------+---------+
5 rows in set (0.00 sec)
mysql> SELECT
   -> id,
   -> name,
   -> SUM(@p1 := CASE
   ->  WHEN in_method = 'Add' THEN value + in_value
   ->  WHEN in_method = 'Subtract' THEN value - in_value
   ->  WHEN in_method = 'Multiply' THEN value * in_value
   ->  WHEN in_method = 'Divide' THEN value / in_value
   -> END) as phase_1,
   -> SUM(CASE
   ->  WHEN out_method = 'Add' THEN @p1 + out_value
   ->  WHEN out_method = 'Subtract' THEN @p1 - out_value
   ->  WHEN out_method = 'Multiply' THEN @p1 * out_value
   ->  WHEN out_method = 'Divide' THEN @p1 / out_value
   -> END) as phase_2
   -> FROM filter
   -> GROUP BY name
   -> ;
+----+------+---------+---------+
| id | name | phase_1 | phase_2 |
+----+------+---------+---------+
|  4 | bar  |   42.00 |  54.625 |
|  1 | foo  |    9.00 |      47 |
+----+------+---------+---------+
2 rows in set (0.00 sec)
So now I'm completely confused. Anyone have an explanation?
____________________________________________________________
Eamon Daly

----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: "Eamon Daly" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 26, 2004 8:15 AM
Subject: Re: Calculating a value based on an aliased column


I thought about using a user variable to store the results of the phase_1
calculation. That would have changed the query to be:
SELECT
@p1 := CASE
 WHEN in_method = 'Add' THEN value + in_value
 WHEN in_method = 'Subtract' THEN value - in_value
 WHEN in_method = 'Multiply' THEN value * in_value
 WHEN in_method = 'Divide' THEN value / in_value
END as phase_1,
CASE
 WHEN out_method = 'Add' THEN @p1 + out_value
 WHEN out_method = 'Subtract' THEN @p1 - out_value
 WHEN out_method = 'Multiply' THEN @p1 * out_value
 WHEN out_method = 'Divide' THEN @p1 / out_value
END as phase_2
FROM filter
Then I continued reading
http://dev.mysql.com/doc/mysql/en/Variables.html
and figured out that each phase_2 calculation will be computed using the
phase_1 result from the PREVIOUS row (as the value is not committed to the
variable until the end of each row's processing). So, this method won't
work either. You may have to actually do this in two steps using either a
temporary table or a subquery to compute the phase_1 results. Here's a
temp table method:
CREATE TEMPORARY TABLE  tmpPhase1
SELECT
ID,
CASE
 WHEN in_method = 'Add' THEN value + in_value
 WHEN in_method = 'Subtract' THEN value - in_value
 WHEN in_method = 'Multiply' THEN value * in_value
 WHEN in_method = 'Divide' THEN value / in_value
END as phase_1,
FROM filter;
SELECT
f.id,
tp.phase_1,
CASE
 WHEN out_method = 'Add' THEN phase_1 + out_value
 WHEN out_method = 'Subtract' THEN phase_1 - out_value
 WHEN out_method = 'Multiply' THEN phase_1 * out_value
 WHEN out_method = 'Divide' THEN phase_1 / out_value
END as phase_2
from filter f
inner join tmpPhase1 tp
on tp.id = f.id;
DROP TEMPORARY TABLE tmpPhase1;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Sorry.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]