How to group using a special condition

Question

Currently, when I issue this SQL, it gets a separate username.

I have several different usernames that represent groups, for example. GRP_BSN .

I would like to group all other usernames (which are numeric) into a group, for example. GRP_OTHERS

 select username, count(*) from host where seq between 0 and 2000 group by username; 63149 1 63732 1 64110 2 70987 12 76841 4 GRP_BSN 226 GRP_ASN 243 GRP_DSC 93 

Can I achieve something like this:

 GRP_OTHERS 20 GRP_BSN 226 GRP_ASN 243 GRP_DSC 93 

EDIT: Modified request from response

 select username, count(*) from host where created_dt -- date selection between to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') and to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss') GROUP BY CASE WHEN REGEXP_LIKE(username, '^\d+$') THEN 'GRP_OTHERS' ELSE username END; 

Answer 1

@bfavaretto is nice (+1 to it), but if you don’t know about the username prefix or they are different, you can go with something like:

 GROUP BY CASE WHEN REGEXP_LIKE(username, '^\d+$') THEN 'GRP_OTHERS' ELSE username END 

Answer 2

Not very effective, but should work:

 SELECT CASE WHEN username LIKE 'GRP%' THEN username ELSE 'GRP_OTHERS' END AS username, COUNT(*) FROM host WHERE seq BETWEEN 0 AND 2000 GROUP BY CASE WHEN username LIKE 'GRP%' THEN username ELSE 'GRP_OTHERS' END; 

Answer 3

If you want to do this by placing small groups in one bucket, instead of a specific name pattern, you can use:

 select (case when cnt > 100 then username else 'OTHER' end), sum(cnt) as cnt from (select username, count(*) as cnt from host where seq between 0 and 2000 group by username ) t group by (case when cnt > 100 then username else 'OTHER' end)