subject:"Calculating a value based on an aliased column"

Re: Calculating a value based on an aliased column

2004-10-26 Thread Harald Fuchs

In article [EMAIL PROTECTED],
Eamon Daly [EMAIL PROTECTED] writes:

 SELECT
 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 phase_1 + in_value
   WHEN out_method = 'Subtract' THEN phase_1 - in_value
   WHEN out_method = 'Multiply' THEN phase_1 * in_value
   WHEN out_method = 'Divide' THEN phase_1 / in_value
 END as phase_2
 FROM filter

 But, of course, that results in ERROR 1054: Unknown column
 'phase_1' in 'field list'. Do I need to create a temporary
 table just to hold all the phase_1 values? Ultimately, I
 want to group by name, so that seems like an awfully
 wasteful step. Am I missing something?

If you have only four different methods, you could use method
combinations in a single CASE statement:

  SELECT CASE
   WHEN in_method = 'Add' AND out_method = 'Add'
   THEN value + in_value + out_value
   WHEN in_method = 'Add' AND out_method = 'Subtract'
   THEN value + in_value - out_value
   ...
 END
  FROM filter


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

Re: Calculating a value based on an aliased column

2004-10-26 Thread SGreen

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

Eamon Daly [EMAIL PROTECTED] wrote on 10/25/2004 05:41:25 PM:

 Okay, I'm at my wit's end on this one. Suppose I have a
 (grossly simplified) table like so:
 
 mysql select * from filter;
 ++--+---+---+--++---+
 | id | name | value | in_method | in_value | out_method | out_value |
 ++--+---+---+--++---+
 |  1 | foo  | 1 | Multiply  |1 | Add| 2 |
 |  2 | foo  | 2 | Divide|3 | Subtract   | 4 |
 |  3 | foo  | 3 | Add   |5 | Multiply   | 6 |
 |  4 | bar  | 4 | Subtract  |7 | Divide | 8 |
 |  5 | bar  | 5 | Multiply  |9 | Add|10 |
 ++--+---+---+--++---+
 
 where in_method and out_method are enums. I want to perform
 a transformation on the initial value using in_method, then
 perform another calculation of that result using out_method,
 like so:
 
 ++--+-+-+
 | id | name | phase_1 | phase_2 |
 ++--+-+-+
 |  1 | foo  |   1 |   3 |
 |  2 | foo  | .67 |   -3.33 |
 |  3 | foo  |   8 |  48 |
 |  4 | bar  |  -3 |   -0.38 |
 |  5 | bar  |  45 |  55 |
 ++--+-+-+
 
 On paper, it's easy:
 
 SELECT
 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 phase_1 + in_value
   WHEN out_method = 'Subtract' THEN phase_1 - in_value
   WHEN out_method = 'Multiply' THEN phase_1 * in_value
   WHEN out_method = 'Divide' THEN phase_1 / in_value
 END as phase_2
 FROM filter
 
 But, of course, that results in ERROR 1054: Unknown column
 'phase_1' in 'field list'. Do I need to create a temporary
 table just to hold all the phase_1 values? Ultimately, I
 want to group by name, so that seems like an awfully
 wasteful step. Am I missing something?
 
 
 Eamon Daly
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Calculating a value based on an aliased column

2004-10-26 Thread 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]

Calculating a value based on an aliased column

2004-10-25 Thread Eamon Daly

Okay, I'm at my wit's end on this one. Suppose I have a
(grossly simplified) table like so:
mysql select * from filter;
++--+---+---+--++---+
| id | name | value | in_method | in_value | out_method | out_value |
++--+---+---+--++---+
|  1 | foo  | 1 | Multiply  |1 | Add| 2 |
|  2 | foo  | 2 | Divide|3 | Subtract   | 4 |
|  3 | foo  | 3 | Add   |5 | Multiply   | 6 |
|  4 | bar  | 4 | Subtract  |7 | Divide | 8 |
|  5 | bar  | 5 | Multiply  |9 | Add|10 |
++--+---+---+--++---+
where in_method and out_method are enums. I want to perform
a transformation on the initial value using in_method, then
perform another calculation of that result using out_method,
like so:
++--+-+-+
| id | name | phase_1 | phase_2 |
++--+-+-+
|  1 | foo  |   1 |   3 |
|  2 | foo  | .67 |   -3.33 |
|  3 | foo  |   8 |  48 |
|  4 | bar  |  -3 |   -0.38 |
|  5 | bar  |  45 |  55 |
++--+-+-+
On paper, it's easy:
SELECT
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 phase_1 + in_value
 WHEN out_method = 'Subtract' THEN phase_1 - in_value
 WHEN out_method = 'Multiply' THEN phase_1 * in_value
 WHEN out_method = 'Divide' THEN phase_1 / in_value
END as phase_2
FROM filter
But, of course, that results in ERROR 1054: Unknown column
'phase_1' in 'field list'. Do I need to create a temporary
table just to hold all the phase_1 values? Ultimately, I
want to group by name, so that seems like an awfully
wasteful step. Am I missing something?

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

Re: Calculating a value based on an aliased column

Re: Calculating a value based on an aliased column

Re: Calculating a value based on an aliased column

Calculating a value based on an aliased column

4 matches

Site Navigation

Mail list logo

Footer information