Not a single-group group function

[PETEHO]

I am getting the above error when writing some code(Not a single-group group function)

select '01 Arrears' as arrears,a.agreement_type as Agreement_type,a.agreement_status as agreement_status ,count(*) as count,a.prb_number as prb_number,a.prb_fund_type as fund_type,
sum(a.wkprem * ((to_date(sysdate,'dd/MM/yy') - trunc(a.paid_clear_to_date)) / 7) - sum(a.part_contribution_amt)) as sum_of_prem
from mi_ddso_wk_equiv_premium_v a
where trunc(a.paid_clear_to_date) < to_date(sysdate,'dd/MM/yy')
and a.agreement_status = 'A'
group by '01 Arrears',a.agreement_type,a.agreement_status,a.prb_number,a.prb_fund_type

However I had this working before fine in business object. I have now added sysdate in to my script to replace some prompts for Business Objects. Script below

select '01 Arrears' as arrears,a.agreement_type as Agrrement_type,a.agreement_status as agreement_status ,count(*) as count,a.prb_number as prb_number,a.prb_fund_type as fund_type,
sum(a.wkprem*((to_date(substr(@Prompt('1-Start Date','d',,mono,free),1,10),'dd/MM/yyyy')- trunc(a.paid_clear_to_date)) / 7))- sum(a.part_contribution_amt) as sum_of_prem
from mi_ddso_wk_equiv_premium_v a
where trunc(a.paid_clear_to_date) < to_date(substr(@Prompt('1-Start Date','d',,mono,free),1,10),'dd/MM/yyyy')
and a.agreement_status = 'A'
group by '01 Arrears',a.agreement_type,a.agreement_status,a.prb_number,a.prb_fund_type

 

[SantaMufasa]

I had this working before fine in business object.

To which set of code does this apply? I haven't desk checked your second set of code, but your first set of code is problematic because, due to your parentheses, your second sum function is nested inside your outer (first) sum function...That is illegal.


Notice that in your second set of code, your second sum function follows (and is not nested inside) the first sum function, which is why your second set of code is probably the working code, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jimirvine]

However I had this working before fine in business object.

1. I would doubt this (in either case of the code) as I'm pretty sure that BO won't accept aliasing in its dynamically created sql.
2. SYSDATE is a date, why would you use a to_date on it? Makes no sense whatsoever.
although, as Santa points out, your main issue seems to be with your brackets.