Select top 5 results then group the rest to "Other"?

Question

How do I view the top 5 results and group the rest under "Other"? If I have a table "visits" with the field "page", it has 100 rows and I need results to be like:

Other    40
/        20
/about   15
/contact 10
/welcome 10
/test     5

I can't manage to do this in one query on my own.

Answer 1

Try

select case when v2.page is not null 
              then v1.page 
              else 'other' 
       end, 
       count(*)
from visits v1
left join 
(
  select page, count(*) as pcount
  from visits
  group by page
  order by pcount desc
  limit 5
) v2 on v1.page = v2.page
group by case when v2.page is not null 
              then v1.page 
              else 'other' 
         end