Page 1 of 1

SQL Query driving me nuts

Posted: Mon Jun 25, 2007 1:09 pm
by thiscatis
Ok, this is what I have so far

Code: Select all

SELECT R.r_id,S.s_type, S.s_name, S.s_region, SUM(R.r_assess1) AS TOTAL_EXERCISE1, SUM(R.r_assess2) AS TOTAL_EXERCISE2
       FROM results AS R INNER JOIN schools AS S 
         ON (R.r_id = S.s_id)  
                     GROUP BY R.r_id

 HAVING SUM(R.r_assess1) > 0

 ORDER BY S.s_region ASC, S.s_type ASC
                       
How can I get the relative improvement between TOTAL_EXERCISE1 and TOTAL_EXERCISE2 as an extra column.
In math it's just [ (TOTAL_EXERCISE2 - TOTAL_EXERCISE1) / (TOTAL_EXERCISE1) ] * 100,
how can I get this extra column in the result sheet?

Posted: Mon Jun 25, 2007 1:21 pm
by thiscatis
not so nuts after all


Code: Select all

SELECT R.r_id,S.s_type, S.s_name, S.s_region, SUM(R.r_assess1) AS TOTAL_EXERCISE1, SUM(R.r_assess2) AS TOTAL_EXERCISE2, (SUM(R.r_assess2)- SUM(R.r_assess1)) / SUM(R.r_assess1) AS TOTALSCORE
       FROM results AS R INNER JOIN schools AS S 
         ON (R.r_id = S.s_id)  
                     GROUP BY R.r_id

 HAVING SUM(R.r_assess1) > 0

 ORDER BY S.s_region ASC, S.s_type ASC