CODEWITHSUNDEEP
sqloracle-database
Trillian
Trillian

Reputation: 411

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"

Upvotes: 0

Views: 71

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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);

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Wrap the case part up as a derived table (the subquery). GROUP BY its result.

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

This way you don't need to repeat the case expression, i.e. less error prone.

Upvotes: 2

Related Questions