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

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

Select allOpen in new window

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.

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

Thanks!

Oops.. Missed checking it earlier
Remove amount_type in your GROUP BY clause..

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!

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

Select allOpen in new window

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

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

Select allOpen in new window

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; 

Select allOpen 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?

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

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

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

And kindly incorporate this in your initial query to make it work..

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

Select allOpen in new window

>> 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..

Still creates two separate records in the result set.  I need one record that shows total DR and total CR.

>> 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.

Thanks for hanging in there with me tonight!  You are amazing!

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