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

What is behind logic ? what relations have the first two rows ? — Oto Shavadze, May 22 '17 at 20:12
Specifically one is "Contact Editor" and one is "Incident Editor" - we want to capture them two as just being under "Editor" — pee2pee, May 22 '17 at 20:14

score 1 · Answer 1 · answered May 22 '17 at 20:18

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

answered May 22 '17 at 20:18

Oto Shavadze

40,603
55
152
236

99% there but it turns out case statements are not permitted! – pee2pee May 22 '17 at 20:27

score 1 · Answer 2 · answered May 22 '17 at 20:22

1

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

answered May 22 '17 at 20:22

Tudor Constantin

26,330
7
49
72

99% there but it turns out case statements are not permitted! – pee2pee May 22 '17 at 20:27
who doesn't permit them? – Tudor Constantin May 22 '17 at 20:28
http://documentation.custhelp.com/euf/assets/devdocs/august2016/Connect_PHP/Content/Connect%20for%20PHP%20API/RightNow%20Object%20Query%20Language/ROQL%20and%20Common%20Objects.htm – pee2pee May 22 '17 at 20:29
this is the first time in my life I hear about this thing. This is not MySQL, this is not even close to MySQL. If I were you, I'd do the sum at the application level, which I assume is written in PHP. I'm not even sure it's possible to do it in ROQL – Tudor Constantin May 22 '17 at 20:35

ROQL Group By Combine Fields

2 Answers2