SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Query help,

  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query help,

    Hi

    I have the following query (which seems to work).
    Code MySQL:
    SELECT i.interest, af.annualfee 
    FROM (SELECT (AVG(pa.pr_att_value)) AS interest FROM pratt AS pa, product AS p  WHERE p.product_id = pa.product_id AND p.pr_status = 1 AND pa.pr_ty_att_label = "Interest Rate") AS i
    , (SELECT (AVG(pa.pr_att_value)) AS annualfee FROM pratt AS pa, product AS p  WHERE p.product_id = pa.product_id AND p.pr_status = 1 AND pa.pr_ty_att_label = "Annual Fee") AS af
    I plan to add further "averages", but before I do this, I was wondering if there was a better (more efficient?) way of doing this?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this is better because it does only one pass of your tables --
    Code:
    SELECT AVG(CASE WHEN pa.pr_ty_att_label = 'Interest Rate'
                    THEN pa.pr_att_value
                    ELSE NULL END) AS interest 
         , AVG(CASE WHEN pa.pr_ty_att_label = 'Annual Fee'
                    THEN pa.pr_att_value
                    ELSE NULL END) AS annualfee 
      FROM pratt AS pa
    INNER
      JOIN product AS p  
        ON p.product_id = pa.product_id 
       AND p.pr_status = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much...Way way better.


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
  •