What is the problem in IF statement in this code?

user_12233289 0
Viewed 31
select studentid,
sum(score) 'Total',
avg(IF(score>85)) 'Average',
max(score) 'Maximum',
min(score) 'Minimum'
from results
group by studentid;

Error Message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) 'Average',

max(score) 'Maximum',

mariadb max sum average sql
2 Answers

You are not using the if() function correctly - and you should not be using single quotes to alias the columns in the resultset.

You might want:

select 
    studentid,
    sum(score) Total,
    avg(score > 85) Average,
    max(score) Maximum,
    min(score) Minimum
from results
group by studentid;

avg(score > 85) gives you the ratio of scores that are above 85, as a decimal number between 0 and 1.

If, on the other hand, you want the average of scores that are above 85, you would do:

    avg(case when score > 85 then score end) Average
user_106767160

If you want the ratio of scores over 85, then use a case expression:

select studentid, sum(score) as Total,
       avg(case when score > 85 then 1 else 0 end) as ratio_over_85,
       max(score) as Maximum, min(score) as Minimum
from results
group by studentid;

Or use the shortcut:

       avg( score > 85 ) as ratio_over_85,

Also, do not use single quotes for column names; only use them for string and date constants.

user_11440350
Related