I have two tables in mysql database

1. subjectids

     id      subject
     11      Physics    
     12      Chemistry  
     13      Maths  
     14      Biology    
     15      History    
     16      Geography  
   

2. studentsScores

     id  student  subjectid   score
       1    Ahaan     11           45
       2    Ahaan     12           33
       3    Ahaan     13           49
       4    Ivaan     11           41
       5    Ivaan     12           38
       6    Ivaan     13           46
       7    Ann       11           40
       8    Ann       12           30
       9    Ann       13           50
   

I am trying to find the average of each subject and give a tag of easy , medium, hard based on the average value, like hard if avg<35, medium if avg between 35 and 45 and easy if avg greater than 45. My expected result is

     subject    subjectid    avg_score   level
     physics      11             42       medium
     chemistry    12             33       hard
     math         13             48       easy

I am new to sql, it would be great if you can help.

CodePudding user response：

A simple JOIN and GROUP BY is enough to get your wanted result

SELECT `subject`, `subjectid`, ROUND(AVG(`score`),0) avg_score,
         CASE 
  WHEN  AVG(`score`)  < 35 THEN 'hard'
            WHEN  AVG(`score`)  between 35  and 45 then 'medium'
            WHEN  AVG(`score`)  > 45 THEN 'easy'
       end as level
FROM studentsScores ss JOIN subjectids si ON ss.`subjectid` = si.`id`
GROUP BY `subject`,`subjectid`

fiddle

CodePudding user response：

A simple case statement would do the trick.

   select si.subject,
       si.id,
       AVG(ss.score) as avg_score,
       case when AVG(ss.score) < 35 then 'hard'
            when AVG(ss.score) between 35  and 45 then 'medium'
            when AVG(ss.score) > 45 then 'easy'
       end as level
from subjectids si 
inner join studentsScores ss on si.id=ss.subjectid
group by si.subject,si.id ;

https://dbfiddle.uk/IDS43R9W