How to group by with a special condition How to group by with a special condition sql sql

How to group by with a special condition


sql oracle group-by

@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

sql oracle group-by

Not very efficient, but should work:

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

sql oracle group-by

If you wanted to do it by putting small groups into one bucket, instead of by a particular name pattern, you could use:

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

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last