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
pee2pee
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
Oto Shavadze
source
to share
select count(*),
case when Category like '%Editor' then 'Editor' else Category end as Category
From MyTable
Group by Category
+1
Tudor Constantin
source
to share