Professional Web Applications Themes

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

    Default 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?

    thanks in advance
    Frits

    Frits Guest

  2. #2

    Default 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. #3

    Default 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;

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

Similar Threads

  1. Making a calculated field...
    By Odvesims in forum MySQL
    Replies: 0
    Last Post: April 11th, 03:06 PM
  2. Insert TAB into calculated field
    By Don in forum FileMaker
    Replies: 3
    Last Post: August 11th, 07:30 AM
  3. Calculated Field in a Form
    By Clifford S. Morton in forum Microsoft Access
    Replies: 1
    Last Post: July 18th, 10:12 PM
  4. Another calculated field question.
    By Microsoft in forum Microsoft Access
    Replies: 1
    Last Post: July 12th, 11:09 AM
  5. Calculated Field - newbie question.
    By Steve Thomas in forum Microsoft Access
    Replies: 6
    Last Post: June 30th, 05:32 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139