Question 21928 in Mysql Question posted in
The official MySQL documentation can be found here.

create a new column based on the conditions applied on the calculated average value in mysql, finally join the tables

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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`
    subject subjectid avg_score level
    Physics 11 42 medium
    Chemistry 12 34 hard
    Maths 13 48 easy

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search