本文介绍了"ORA-00937:不是单组分组功能".在运行我的查询时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用Oracle 10g.我的查询是:
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"
我收到此错误.
ORA-00937: not a single-group group function
我在这里想念什么?
推荐答案
这是您的查询:
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";
您具有嵌套的聚合功能.尽管Oracle允许这样做,但我怀疑您是否真的打算使用该功能.我的猜测是您真的想要这样的东西:
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";
您可能希望改用count(distinct)
:
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:不是单组分组功能".在运行我的查询时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!