SQL Query driving me nuts

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

SQL Query driving me nuts

Post 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?
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post 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
Post Reply