help needed with SQL code [message #663889] |
Fri, 23 June 2017 02:59 |
aadebayo
Messages: 38 Registered: August 2005
|
Member |
|
|
Hi All
I am currently attempting t run the SQL code below, but I am getting an error message
Quote:ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"
*Cause:
*Action:
Error at Line: 7 Column: 7
The code is below
select Successful_calls
,unSuccessful_calls
,device_id
from (select
nvl(SUM(case when l17.response = '200' then 1 end), 0) AS Successful_calls
,nvl(SUM(case when l17.response <> '200' then 1 end), 0) AS unSuccessful_calls
,l17.device_id
,count(l17.device_id) over (partition by l17.device_id order by created_timstm desc)
from activity l17)
group by Successful_calls
,unSuccessful_calls
,device_id;
Please help me identify where I am going wrong.
[Updated on: Fri, 23 June 2017 03:16] Report message to a moderator
|
|
|
|
|
|
Re: help needed with SQL code [message #663894 is a reply to message #663891] |
Fri, 23 June 2017 05:30 |
aadebayo
Messages: 38 Registered: August 2005
|
Member |
|
|
Thanks for your helps Michel and CookieMonster. I have managed to narrow where the problem is.
I have modified the code as below and the bit that is causing the issue is
rank() over (partition by item order by created_time desc) as rnk
The code runs when that line is commented out. So please help me with why this is causing the error. The exact spot is item in the code above
select ToTal_Calls
,Total_Successful
,Total_unSuccessful
from
(select item
created_time,
reqest_in,
reqest_uri,
count(distinct(item)) AS Total_Calls,
nvl(SUM(case when response = 'VERIFIED' then 1 end), 0) Total_Successful,
nvl(SUM(case when response <> 'VERIFIED' then 1 end), 0) AS Total_unSuccessful--,
rank() over (partition by item order by created_time desc) as rnk
from activity l17
where reqest_uri ='/api/v1/item/*/register'
and trunc(created_time) between trunc(sysdate-1) and trunc(sysdate)
and item in (select distinct substr(item,2,16) from vehicle)
and reqest_in = 'Y'
);
|
|
|
|
Re: help needed with SQL code [message #663896 is a reply to message #663895] |
Fri, 23 June 2017 06:08 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That query definitely needs a group by, so I don't see how it can possibly run if you comment out the rank.
And if you just comment out the comma as in the code you actually posted - that'd be invalid syntax.
|
|
|
Re: help needed with SQL code [message #663899 is a reply to message #663896] |
Fri, 23 June 2017 09:13 |
aadebayo
Messages: 38 Registered: August 2005
|
Member |
|
|
Thanks for all your help. I have managed to get the code to work (with the help of one of my colleagues.) below is the working code, in case it benefits somebody else
select count(distinct l17.item_id) as total_calls,
nvl(sum(case when response = '200' and error_cd = 'VERIFIED' then 1 else 0 end), 0) as successful_calls,
nvl(sum(case when (response = '200' and error_cd <> 'VERIFIED') or (response <> '200') then 1 else 0 end), 0) as unsuccessful_calls
from reqAct l17
where l17.uri_val ='/api/v1/items/*/register'
and l17.start_date >= trunc(sysdate) -1
and l17.start_date < trunc(sysdate)
and l17.item in (select distinct substr(id,2,16) from vehicle)
and l17.in_flag = 'Y'
and error_cd is not null;
[Updated on: Fri, 23 June 2017 09:13] Report message to a moderator
|
|
|