CODEWITHSUNDEEP
sql
J2015
J2015

Reputation: 330

Return result of a comparison in a query

I am working on a query which is between the following tables:

tasks:

    id | name
  -----+-------------
   101 | MinDist
   123 | Equi
   142 | Median
   300 | Tricoloring

reports:

   id | task_id  | candidate         | score
  ----+----------+-------------------+--------
   13 | 101      | John Smith        | 100
   24 | 123      | Delaney Lloyd     | 34
   37 | 300      | Monroe Jimenez    | 50
   49 | 101      | Stanley Price     | 45
   51 | 142      | Tanner Sears      | 37
   68 | 142      | Lara Fraser       | 3
   83 | 300      | Tanner Sears      | 0

My output should be like:

    task_id | task_name    | difficulty
   ---------+--------------+------------
        101 | MinDist      | Easy
        123 | Equi         | Medium
        142 | Median       | Hard
        300 | Tricoloring  | Medium

And all I have done so far is this, however does not work:

SELECT rp.task_id, tasks.name, difficulty 
FROM tasks 
JOIN reports AS rp
WHERE   
    case when AVG(rp.score) < 20 then difficulty 'Hard' end as difficulty,
    case when 20 < AVG(rp.score) < 60 then difficulty 'Medium' end as difficulty,
    case when 60 < AVG(rp.score)  then difficulty 'Easy' end as difficulty,
GROUPBY rp.task_id;

and I get this error:

Output (stderr): syntax error at or near "WHERE" LINE 5: WHERE
^

Upvotes: 0

Views: 2037

Answers (2)

abhijitcaps
abhijitcaps

Reputation: 594

With respect to your output, your SQL should be

enter image description here


Note: Sorry, I am pasting SQL as image, as stackoverflow is giving some indentation error with the SQL query as text while I am trying to submit my comment, which I am unable to resolve.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to define the column in the SELECT:

SELECT t.id, t.name, 
       (case when AVG(rp.score) < 20 then 'Hard'
             when AVG(rp.score) < 60 then 'Medium' 
             else 'Easy'
        end) as difficulty
FROM tasks t JOIN
     reports rp
     ON t.id = rp.task_id
GROUP BY t.id, t.name;

Upvotes: 3

Related Questions