"ORA-00937: not a single-group group function" when running my query

Question

i am using oracle 10g. my query is :

select "Debtor"."DebtorName",
sum(case when regionTable."Text" like '%North%' 
then to_number(count("Registration"."pkRegistrationId")) else 0 end) "North",
 sum(case when regionTable."Text" like '%South%' 
then to_number(count("Registration"."pkRegistrationId")) else 0 end) "South"
from "Registration"
inner join "CustomerRequisition" on "CustomerRequisition"."pkCustomerRequisitionId"="Registration"."fkCustomerRequisitionId"
inner join "EnumerationValue" regionTable on regionTable."pkEnumerationValueId"="CustomerRequisition"."fkRegion"
inner join "Debtor" on "Debtor"."pkDebtorId"="CustomerRequisition"."fkDebtorId"
group by "Debtor"."DebtorName",regionTable."Text","Registration"."pkRegistrationId"

i am getting this error.

ORA-00937: not a single-group group function

what am i missing here?

http://stackoverflow.com/search?q=ORA-00937%3A+not+a+single-group+group+function — , Aug 29 '14 at 10:38
I didn't downvote but you clearly have made no attempt to find a solution so it's no surprise this got downvoted. (see the link I posted there are literally hundreds of questions regarding this error) — , Aug 29 '14 at 10:47
solution is to put the same column names in the group by clause which are present in "select". i did that but getting the same error. and google search telling me the same thing repeatedly. so i raised a question here. — Khurram Zulfiqar Ali, Aug 29 '14 at 10:50
possible duplicate of [ORA-00979 not a group by expression](http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) — Ben, Aug 29 '14 at 10:52
No, you haven't. The only thing that's not in an aggregate function here is `"Debtor"."DebtorName"`, which means it should be the only thing in the GROUP BY. P.S., why is everything quoted? It makes you life a lot more difficult if you only have quoted names. — Ben, Aug 29 '14 at 10:54
@Ben It my database and want each table and column to be case sensitive that is why double quotes because oracle do a .ToUpper() type thing in every table package or column name which i dont want. further more i did that already and got the same error when "ONLY" "Debtor"."DebtorName" is present in my "group by" clause. — Khurram Zulfiqar Ali, Aug 29 '14 at 10:57

score 2 · Accepted Answer · answered Aug 29 '14 at 11:03

This is your query:

select "Debtor"."DebtorName",
       sum(case when regionTable."Text" like '%North%' 
                then to_number(count("Registration"."pkRegistrationId")) else 0
           end) "North",
       sum(case when regionTable."Text" like '%South%' 
                then to_number(count("Registration"."pkRegistrationId")) else 0
           end) "South"
from "Registration" inner join
     "CustomerRequisition
     on "CustomerRequisition"."pkCustomerRequisitionId" = "Registration"."fkCustomerRequisitionId" inner join
     "EnumerationValue" regionTable    
     on regionTable."pkEnumerationValueId"="CustomerRequisition"."fkRegion" inner join
     "Debtor"
     on "Debtor"."pkDebtorId"="CustomerRequisition"."fkDebtorId"
group by "Debtor"."DebtorName", regionTable."Text", "Registration"."pkRegistrationId";

You have nested aggregation functions. Although Oracle does allow this, I doubt you really intend that feature. My guess is that you really want something like:

select "Debtor"."DebtorName",
       sum(case when regionTable."Text" like '%North%' 
                then 1 else 0
           end) "North",
       sum(case when regionTable."Text" like '%South%' 
                then 1 else 0
           end) "South"
from "Registration" inner join
     "CustomerRequisition"
     on "CustomerRequisition"."pkCustomerRequisitionId" = "Registration"."fkCustomerRequisitionId" inner join
     "EnumerationValue" regionTable    
     on regionTable."pkEnumerationValueId" = "CustomerRequisition"."fkRegion" inner join
     "Debtor"
     on "Debtor"."pkDebtorId" = "CustomerRequisition"."fkDebtorId"
group by "Debtor"."DebtorName";

It is possible that you want count(distinct) instead:

select "Debtor"."DebtorName",
       count(distinct case when regionTable."Text" like '%North%' 
                      then "Registration"."pkRegistrationId"
           end) "North",
       count(distinct case when regionTable."Text" like '%South%' 
                      then "Registration"."pkRegistrationId"
           end) "South"

"ORA-00937: not a single-group group function" when running my query

1 Answers1

Linked