SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Avoid same multiple calculations in a single query

    Let's say I have the following query in mysql:
    Code:
    SELECT net_price,
     net_price*tax AS price,
     net_price*tax*qty AS total,
     net_price*tax*qty*discount AS discount_total
     FROM product
    In this sample only the first returned column is actual data from the table, the rest are values calculated on the fly. But as you can see each calculated field continues calculation from the previous field. So theoretically I might rewrite the query like this:

    Code:
    SELECT net_price,
     net_price*tax AS price,
     price*qty AS total,
     total*discount AS discount_total
     FROM product
    except that this won't work because there are no columns like `price` and `total`.

    This is a simple example but sometimes I need to perform much more complex calculations and then use them as the basis for other calculated fields. So far I have used one of the two solutions:

    1. Repeat the calculations like in the first code above.
    2. Make several nested select queries (derived tables).

    Both solutions are ugly to me because they require either repetition of code or nesting queries many levels deep - bad for readability and possibly on performance, too. Is there a better solution?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no
    That was not the answer I wanted to hear but thanks anyway .

    BTW, do you know if mysql optimizer is smart enough to notice there are multiple identical expressions and evaluate them only once for performance?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    BTW, do you know if mysql optimizer is smart enough to notice there are multiple identical expressions and evaluate them only once for performance?
    i'm sorry, i don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •