How to write sql query or stored procedure to calculate average with different no. of obseravations to get single resultset.

searchsanjaysharma used Ask the Experts™
I have 9 subjectcode, in which every subjectcode contains the subquestions as follows.Here question 1-7 contains 12 sub questions, and 8 contains 10 subquestions, and question 9 contains 8 sub-questions, where max score that can be obtained for per subquestion is 100
and max score that can be obtained for subjectcode 1-7 is 1200, 8 is 1000 and 9 is 800.
To calculate average of subjectcode i am writing
select subjectcode,sum(totalscore)/count(*)*12 from t
group by subjectcode

Here totalscore is the totalscore of all subquestions in everysubject.
But i want this query to return a single recordset with subjectcode 8 to be divided by 10 and 9 to be divided by 8.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Give this a go.

select subjectcode,1.0*sum(totalscore)/count(*)/case subjectcode when 8 then 10 when 9 then 8 else 12 end
from t
group by subjectcode



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial