Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: sum() mysql columns

    I got table name worksheet.

    I get the output using this query in my php page.

    $result = mysql_query("SELECT * , `issuedwt` - `receiptwt` AS `workloss` FROM `worksheet`")

    But i need the 'workloss' field value to be inserted in mysql table.

    Can any one help in this, please
    Attached Thumbnails Attached Thumbnails sample.jpg  

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your subject was
    sum() mysql columns
    But, I couldn't see sum() in your query.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    But i need the 'workloss' field value to be inserted in mysql table.
    it does kind of beg the question why?

    there's several approaches
    run an update statement
    UPDATE mytable SET mycolumn = columna - columnb

    then you need to run that stateemtn every time you change a value in columna or b for the row(s) that have just been changed

    do it in the frnt end, the thing thatr captures the issuedwt` & `receiptwt from the user

    but this is derived data, you can get to that value whenever you require, so storing it violates one of the fundamental principles behind relational databases. with modern day triggers and stored procedures you can reduce the risks associated with violating such principles (ie user programs call the stored procedure which behind the scenes updates the calculated value. but you are still exposed to your data being out of step if soemone makes changes to the table
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2013
    Posts
    1
    As mentioned before; this is derived data and should therefor not be stored in the original table.

    However, you may want to store it somewhere if you need to do searches on the values (other databases would simply use a functional index for that) This is where materialized views can help; they are basically a table that holds derived data (or data that is expected to be "derived") which is maintained by triggers or stored functions. Your application writes to the underlying tables and the database updates the view with the new values automatically. This keeps your source tables free of derived data and still gives access to the derived data as if it was regular data.

Posting Permissions

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