# making a calculated field (Newbie) - MySQL

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

1. ## making a calculated field (Newbie)

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?

Frits

Frits Guest

2. ## Re: making a calculated field (Newbie)

>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
Gordon Burditt Guest

3. ## Re: making a calculated field (Newbie)

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;

>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)
Kees Nuyt Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•