Another 'ORA-00937: not a single-group group function' question

Cheryl McCormick
Cheryl McCormick used Ask the Experts™
on
Hello Gurus,
Sorry, another 'ORA-00937: not a single-group group function' question.  Obviously, I don;t understand the underlying issues here.  I am trying to run the code below and receive the infamous 'ORA-00937: not a single-group group function' error.  Could someone please assist and explain the rationale...maybe then it will sink in!

Many thanks!
select distinct 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals' as NAME,
'P' as Status,
'Y' as Status_Verified,
'A' as Actual_flag,
'N' as Average_Journal_flag,
'N' as Budgetary_Control_Status,
'Z' as Approval_Status_Code,
'1' as Created_by,
YEAR||PERIOD as Default_Period_Name,
LINE_DESC as Description, 
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from GL_JE_EXTRACTS
where status = 'P'
group by period, amount_type, line_desc

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Try this one out
select 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals' as NAME,
'P' as Status,
'Y' as Status_Verified,
'A' as Actual_flag,
'N' as Average_Journal_flag,
'N' as Budgetary_Control_Status,
'Z' as Approval_Status_Code,
'1' as Created_by,
YEAR||PERIOD as Default_Period_Name,
LINE_DESC as Description, 
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from GL_JE_EXTRACTS
where status = 'P'
group by period, amount_type, line_desc, YEAR

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This contains good explanation to deal with this error

http://ora-00937.ora-code.com/

Always make sure that non-aggregate columns ( columns that are not part of SUM or COUNT or MIN or Max or some other aggregate function) in the SELECT query should be present in the GROUP BY clause to avoid this error.
Cheryl McCormickConsultant

Author

Commented:
Thanks for the quick reply.  The code above returns the same error.  Any other ideas?  I'll check out the link right now!

Thanks!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Oops.. Missed checking it earlier
Remove amount_type in your GROUP BY clause..
Cheryl McCormickConsultant

Author

Commented:
This is what I am executing right now...
select 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals' as NAME,
'P' as Status,
'Y' as Status_Verified,
'A' as Actual_flag,
'N' as Average_Journal_flag,
'N' as Budgetary_Control_Status,
'Z' as Approval_Status_Code,
'1' as Created_by,
YEAR||PERIOD as Default_Period_Name,
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from GL_JE_EXTRACTS
where status = 'P'
group by period,YEAR

I receive the same error and TOAD is highlighting the PERIOD column in line 2 [not the Period in the text but the column].  
Thanks!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Try this one..
select 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals' as NAME,
'P' as Status,
'Y' as Status_Verified,
'A' as Actual_flag,
'N' as Average_Journal_flag,
'N' as Budgetary_Control_Status,
'Z' as Approval_Status_Code,
'1' as Created_by,
YEAR||PERIOD as Default_Period_Name,
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from GL_JE_EXTRACTS
where status = 'P'
group by 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals', YEAR||PERIOD

Open in new window

Cheryl McCormickConsultant

Author

Commented:
Same error, same place.  We are missing something very simple... I tried reducing the query to this very simple version and I still receive the same error on 'PERIOD'...

select distinct PERIOD,
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'
group by PERIOD, amount_type
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This should work
select PERIOD,
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'
group by PERIOD

Open in new window

Cheryl McCormickConsultant

Author

Commented:
Same error and TOAD highlights 'PERIOD' in the SELECT as the error point....  this query works but doesn't return everything I need....  I add anything to the SELECT and the GROUP By and it return the error......

select
max(decode(amount_type, 'D',SUM(amount))) as RUNNING_TOTAL_DR,
max(decode(amount_type, 'C',SUM(amount))) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'
group by amount_type

select distinct 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals' as NAME,
'P' as Status,
'Y' as Status_Verified,
'A' as Actual_flag,
'N' as Average_Journal_flag,
'N' as Budgetary_Control_Status,
'Z' as Approval_Status_Code,
'1' as Created_by,
YEAR||PERIOD as Default_Period_Name,
LINE_DESC as Description, 
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from GL_JE_EXTRACTS
where status = 'P'
group by 'Period '||PERIOD||' '||YEAR||',
'P' ,
'Y' ,
'A' ,
'N' ,
'N' ,
'Z' ,
'1' ,
YEAR||PERIOD,
LINE_DESC;  

OR this should work.... 
select distinct 'Period '||PERIOD||' '||YEAR||' - Adjusting Journals' as NAME,
'P' as Status,
'Y' as Status_Verified,
'A' as Actual_flag,
'N' as Average_Journal_flag,
'N' as Budgetary_Control_Status,
'Z' as Approval_Status_Code,
'1' as Created_by,
YEAR||PERIOD as Default_Period_Name,
LINE_DESC as Description, 
SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
from GL_JE_EXTRACTS
where status = 'P'
group by 'Period '||PERIOD||' '||YEAR||',
YEAR||PERIOD,
LINE_DESC; 

Open in new window

Not sure what are you trying to achieve in your second query... but you cannot SUM the max values...

SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR

Are you trying some cumulative totals?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Ritesh..
    You got that..

I just checked the final SUM statement outside and haven't looked inside deeply

hypermac,
    Kindly let us know what exactly you are trying to achieve with this statement.

SUM(max(decode(amount_type, 'D',(amount)))) as RUNNING_TOTAL_DR,
SUM(max(decode(amount_type, 'C',(amount)))) as RUNNING_TOTAL_CR
Cheryl McCormickConsultant

Author

Commented:
Thanks everyone.  I have a table that contains many records for a single period.  I want to SUM where the account_type = 'D' and also where the account_type = 'C' and show this in a single record for the period.  This query returns the correct SUM values...

select
max(decode(amount_type, 'D',SUM(amount))) as RUNNING_TOTAL_DR,
max(decode(amount_type, 'C',SUM(amount))) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'
group by amount_type
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Then it should be

select
sum(case when amount_type = 'D' then amount else 0 end ) as RUNNING_TOTAL_DR,
sum(case when amount_type = 'C' then amount else 0 end ) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'
group by amount_type
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
And kindly incorporate this in your initial query to make it work..
Cheryl McCormickConsultant

Author

Commented:
And then I have two records - one for RUNNING_TOTAL_DR and one for RUNNING_TOTAL_CR.  I need them on the same line....
Try this
select
sum(decode(amount_type, 'D',amount)) as RUNNING_TOTAL_DR,
sum(decode(amount_type, 'C',amount)) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'
group by amount_type

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> And then I have two records - one for RUNNING_TOTAL_DR and one for RUNNING_TOTAL_CR.  I need them on the same line....

Kindly try out the query in my comment 25631063 or Ritesh's comment 25631097
Both are one and the same except it uses Case and Decode syntaxes..
Cheryl McCormickConsultant

Author

Commented:
Still creates two separate records in the result set.  I need one record that shows total DR and total CR.
Sorry... try this....
select
sum(decode(amount_type, 'D',amount)) as RUNNING_TOTAL_DR,
sum(decode(amount_type, 'C',amount)) as RUNNING_TOTAL_CR
from HFM_GL_JE_EXTRACTS
where status = 'P'

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Still creates two separate records in the result set.

Above query should bring both DR and CR in a single row..
Try using grid view to view the result and confirm.
Cheryl McCormickConsultant

Author

Commented:
Thanks for hanging in there with me tonight!  You are amazing!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Doesn't my query in comment 25631063 provided the correct result set..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial