making a calculated field (Newbie) - MySQL

[Frits]

Hello,

I have a source-field and now I want to make an extra calculated-field
like the following example:

| Source|Calculated|
+-------+----------+
| 10.4 | |
| 10.6 | 0.2 |
| 10.61 | 0.01 |
| 10.67 | 0.06 |
| 10.68 | 0.01 |
| 10.69 | 0.01 |
| 10.69 | 0 |
| 10.69 | 0 |
| 10.7 | 0.01 |
| 10.7 | 0 |
| 10.7 | 0 |
| 10.8 | 0.1 |
| 10.84 | 0.04 |
| 10.85 | 0.01 |
| 10.85 | 0 |
| 11.31 | 0.46 |
+-------+----------+

In a spreadsheet it is easy, B2=A2-A1, B3=A3-A2, B4=A4-A3 etc.
Is it possible to do something like that using only mysql ?
If so , how does the code look like?

thanks in advance
Frits

[Gordon Burditt]

>I have a source-field and now I want to make an extra calculated-field

>like the following example:

You can set the calculated field from the source field:

update table set calculated=big_messy_formula(Source);

which will *NOT* update Calculated if you change Source with
UPDATE or by inserting more rows.

Or, you can select the field when you want to use it:

select *, big_messy_formula(Source) as Calculated from table;

Here, big_messy_formula(Source) represents a formula using Source
and perhaps other variables. It doesn't *have* to be a user-defined
function.

In later versions of MySQL (> 5.0), you can use triggers to
update Calculated whenever Source changes.

Gordon L. Burditt

[Kees Nuyt]

On Sun, 30 Apr 2006 16:37:57 +0200, Frits
<fritsaway.hp1.met.wau.nl> wrote:

>Hello,
>
>I have a source-field and now I want to make an extra calculated-field
>like the following example:
>
>| Source|Calculated|
>+-------+----------+
>| 10.4 | |
>| 10.6 | 0.2 |
>| 10.61 | 0.01 |
>| 10.67 | 0.06 |
>| 10.68 | 0.01 |
>| 10.69 | 0.01 |
>| 10.69 | 0 |
>| 10.69 | 0 |
>| 10.7 | 0.01 |
>| 10.7 | 0 |
>| 10.7 | 0 |
>| 10.8 | 0.1 |
>| 10.84 | 0.04 |
>| 10.85 | 0.01 |
>| 10.85 | 0 |
>| 11.31 | 0.46 |
>+-------+----------+
>
>In a spreadsheet it is easy, B2=A2-A1, B3=A3-A2, B4=A4-A3 etc.
>Is it possible to do something like that using only mysql ?

So, you want to calcultate a column some row from the value of
the same row and the vlaue of some other row.
Rows in a table are in no particular order. The way you can
relate different rows depends on the key columns you have
available. They should contain some clue to distinguish the
predecessor / successor ralation.

>If so , how does the code look like?

Part of it would be a join of the table to itself.
Writing a query to show the calculated result would be easier
than trying to update the table with those values.

not tested:

<some select to get the first row>
UNION
SELECT T1.col1,(T1.col1 - T2.col1) AS col2
FROM yourtable AS T1 INNER JOIN yourtable AS T2
ON T2.predecessor = T1.uniquekey;

>thanks in advance
>Frits

Hope this helps.
--
( Kees
)
c[_] Computers will never take the place of books. You can't stand on a floppy disk to reach a high shelf. (#106)