SQL group by with condition

Imagine i have a table like below:

ID    Score    someOtherColumns
 1        1    ....
 2        1    .....
 3        4    ....
 5        2    ....
 6        1    ....

where score can have an intger value between 0 and 100. I want to group by score, which can be done with something like:

Select score, count(*) from myTable group by score;

How do I add a condition in the group by if I want to group scores > 20 in one group?

I have tried:

Select score, count(*) from myTable
group by 
  case 
    when score >19 then 20
    else score
  end;

but get a “not a GROUP BY expression”

Answer

You need to repeat the same expression with SELECT statement :

Select (case when score > 19 then 20 else score end) as score, count(*) 
from myTable
group by (case when score > 19 then 20 else score end);