ROQL Group By Combine Fields

Question

I'm using Oracle RightNow which uses MySQL but can't have nested queries and only a select (pun!) number of commands at my disposal: http://documentation.custhelp.com/euf/assets/devdocs/august2016/Connect_PHP/Content/Connect%20for%20PHP%20API/RightNow%20Object%20Query%20Language/ROQL%20and%20Common%20Objects.htm

CASE statements are not permitted

Let's say I have

Select Count(*) as Amount, Category 
From MyTable
Group by Category

All is well and I get a table as below

Amount | Category
---------------------
1      | Contact Editor
4      | Incident Editor
787    | Raise a Request
78     | Pending Information

How would I need to amend my query so I could combine the first two rows to have a new updated table as

Amount | Category
---------------------
5      | Editor
787    | Raise a Request
78     | Pending Information

Thanks

Answer 1

Try grouping using case expression:

select Count(*) as Amount,  case when Category in('Contact Editor', 'Incident Editor') then 'editor' else Category end
From MyTable
Group by case when Category in('Contact Editor', 'Incident Editor') then 'editor' else Category end

Answer 2

select count(*), 
case when Category like '%Editor' then 'Editor' else Category end as Category
From MyTable
Group by Category