如何根据特殊情况进行分组 [英] How to group by with a special condition

问题描述

目前，当我发出这个SQL时，它会得到不同的用户名。

Currently when I issue this SQL, it gets the distinct username.

我有一些截然不同的用户名，代表组。 GRP_BSN 。

I have some distinct usernames, which represent groups, e.g. GRP_BSN.

我想将所有其他用户名（恰好是数字） GRP_OTHERS

I would like to group all the other usernames (which happens to be numeric) into a group e.g. 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

$ b $ p 编辑：修改来自答案的查询

Modified query from answer

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;

推荐答案

@bfavaretto很好（+1给他），但如果你不知道用户名前缀或他们不同，你可以用类似的东西：

@bfavaretto is nice (+1 to him), but if you don't know about 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

这篇关于如何根据特殊情况进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！