CODEWITHSUNDEEP
mysql
Masaba James Moses
Masaba James Moses

Reputation: 602

How to get average value from another average using MySQL

I have a Table that stores subjects. Each subject has Papers say Paper 1,2,3... and each paper has several tests that are undertaken like say TEST 1, 2, 3 ..like that.

Now what I want is to get the Average of TESTS undertaken in a particular paper which I have managed to get as below in Average Mark column;

Output so far.

I need help on how to get the average of the paper taken like an average of Paper 1 and Paper 2. to be put in paper_avarage column which should, in this case, be 32.5 but I'm getting 30.00 which is wrong

This is my code sofar which is giving me the average that is slightly wrong.

SELECT SubjectCode
     , PaperNo
     , TestNo
     , MarkScored
     , AverageMark
     , (SELECT avg(AverageMark) As avMark 
          FROM test_results
         WHERE AdminNo = 'SPS-20-O-0003' 
           AND SubjectCode = 112
           AND StudyYear = 2020  
           AND StudyTerm = 1 
           AND StudyClass = 1) as paper_average
  FROM test_results  
 WHERE AdminNo = 'SPS-20-O-0003' 
   AND SubjectCode = 112
   AND StudyYear = 2020  
   AND StudyTerm = 1 
   AND StudyClass = 1 
 ORDER 
    BY PaperNo ASC

Thanks for your help in advance.

Upvotes: 0

Views: 34

Answers (1)

forpas
forpas

Reputation: 164099

You want an average of averages, so replace your subquery with this:

(
  SELECT AVG(t.avMark) AS avMark 
  FROM (
    SELECT AVG(AverageMark) AS avMark 
    FROM test_results
    WHERE AdminNo = 'SPS-20-O-0003' 
      AND SubjectCode = 112
      AND StudyYear = 2020  
      AND StudyTerm = 1 
      AND StudyClass = 1
    GROUP BY PaperNo
  ) AS t
) AS paper_average

Upvotes: 1

Related Questions