Clever Geek Handbook

ROQL group by union fields

I am using Oracle RightNow which uses MySQL but cannot have nested queries and only select (pun!) The 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 allowed

Let's say I have

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

Everything is good and I get the table below

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

How do I need to amend my query so that I can concatenate the first two rows with a new updated table as

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

thank

+3


source to share


2 answers


Try grouping using an expression case

:



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
+1


source 


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


+1


source






More articles:


All Articles