Query help,

Databases
1

Hi

I have the following query (which seems to work).


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

this is better because it does only one pass of your tables –

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
3

Thank you so much…Way way better.