Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: using a calculated field in another caculation

    Hi there,
    I am new to mysql.
    My DB has a table named tbl1 of 5 number-type fields : num1,num2,num3,num4,num5
    my sql Query is as the following:

    SELECT
    num1,
    num2,
    num1+num2 AS num3,
    (num3+10)*2 AS num4,
    num4 / 100 AS num5,
    ID
    FROM tbl1

    the problem is that the fields num4 and num5 return zero.WHat is wrong with my query? Please help.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    I suspect that you have entries in the table that do not contain numeric values but instead contain NULL's. This is a special case in SQL. For instance, 1+0 = 1, but 1+NULL = NULL (or undefined). If the NULL values are to be interpreted as 0 then you must explicitly state this in your query using the IFNULL function.

    Also looking at your query you are calling the resulting rows with names that appear to belong to the different fields in the table. So it is not clear whether you are looking to reuse the previously calculated num3 i.e. (num1 + num2) or the value of the num3 field in the table. SQL will always use the field name and never the alias.
    Last edited by it-iss.com; 09-27-10 at 19:44.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    SELECT num1
         , num2
         , num1+num2 AS num3
         , (num3+10)*2 AS num4
         , num4 / 100 AS num5
         , ID
      FROM tbl1
    to this --
    Code:
    SELECT num1
         , num2
         , num1+num2 AS num3
         , (num1+num2+10)*2 AS num4
         , (num1+num2+10)*2 / 100 AS num5
         , ID
      FROM tbl1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2010
    Posts
    4
    you were a great help
    it works now
    but how can i use ifnull in the statement

Posting Permissions

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