Home » SQL & PL/SQL » SQL & PL/SQL » help needed with SQL code (Oracle, 11, windows)
help needed with SQL code [message #663889] Fri, 23 June 2017 02:59 Go to next message
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 #663890 is a reply to message #663889] Fri, 23 June 2017 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

SQL> select 117.ename from emp 117;
select 117.ename from emp 117
                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended
Re: help needed with SQL code [message #663891 is a reply to message #663890] Fri, 23 June 2017 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 12956
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel - his alias starts with an L not a 1. It's valid.

aadebayo - Your group by needs to be in the select that uses aggregates - the inner one.

Re: help needed with SQL code [message #663892 is a reply to message #663891] Fri, 23 June 2017 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah! The usual error for bad eyes and so the importance of the test case.

Re: help needed with SQL code [message #663894 is a reply to message #663891] Fri, 23 June 2017 05:30 Go to previous messageGo to next message
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 #663895 is a reply to message #663894] Fri, 23 June 2017 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Fri, 23 June 2017 10:21

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: help needed with SQL code [message #663896 is a reply to message #663895] Fri, 23 June 2017 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 12956
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 Go to previous message
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

Previous Topic: PRMDB-0933-1
Next Topic: not gettting output for showplanfrpspreadsheetcode11g
Goto Forum:
  


Current Time: Tue Dec 12 02:44:46 CST 2017

Total time taken to generate the page: 0.02033 seconds