SQL 如何解决 ORA-00937：计算百分比时不是单组组函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2150511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve ORA-00937: not a single-group group function when calculating percentage?
提问by Liao
I'm trying to get a percentage of the itemid that are available in a certain area.
Using my query, I get an error ORA-00937: not a single-group group function
我正在尝试获取某个区域中可用的 itemid 的百分比。使用我的查询，我收到一个错误ORA-00937: not a single-group group function
All the details:
所有细节：
I have these two tables:
我有这两个表：
ALLITEMS
---------------
ItemId | Areas
---------------
1 | EAST
2 | EAST
3 | SOUTH
4 | WEST
CURRENTITEMS
---------------
ItemId
---------------
1
2
3
and want this result:
并想要这个结果：
---------------
Areas| Percentage
---------------
EAST | 50 --because ItemId 1 and 2 are in currentitems, so 2 items divided by the total 4 in allitems = .5
SOUTH | 25 --because there is 1 item in currentitems table that are in area SOUTH (so 1/4=.25)
WEST | 0 --because there are no items in currentitems that are in area WEST
The DDL:
DDL：
drop table allitems;
drop table currentitems;
Create Table Allitems(ItemId Int,areas Varchar2(20));
Create Table Currentitems(ItemId Int);
Insert Into Allitems(Itemid,Areas) Values(1,'east');
Insert Into Allitems(ItemId,areas) Values(2,'east');
insert into allitems(ItemId,areas) values(3,'south');
insert into allitems(ItemId,areas) values(4,'east');
Insert Into Currentitems(ItemId) Values(1);
Insert Into Currentitems(ItemId) Values(2);
Insert Into Currentitems(ItemId) Values(3);
My Query:
我的查询：
Select
areas,
(
Select
Count(Currentitems.ItemId)*100 / (Select Count(ItemId) From allitems inner_allitems Where inner_allitems.areas = outer_allitems.areas )
From
Allitems Inner_Allitems Left Join Currentitems On (Currentitems.Itemid = Inner_Allitems.Itemid)
Where inner_allitems.areas = outer_allitems.areas
***group by inner_allitems.areas***
***it worked by adding the above group by***
) "Percentage Result"
From
allitems outer_allitems
Group By
areas
The error:
错误：
Error at Command Line:81 Column:41 (which is the part `(Select Count(ItemId) From allitems inner_allitems Where inner_allitems.areas = outer_allitems.areas )`)
Error report:
SQL Error: ORA-00937: not a single-group group function
When I run the exact same query in SQL Server, it works fine. How do I fix this in Oracle?
当我在 SQL Server 中运行完全相同的查询时，它工作正常。如何在 Oracle 中解决此问题？
回答by Jeffrey Kemp
Analytics are your friend:
分析是您的朋友：
SELECT DISTINCT
areas
,COUNT(currentitems.itemid)
OVER (PARTITION BY areas) * 100
/ COUNT(*) OVER () Percentage
FROM allitems, currentitems
WHERE allitems.itemid = currentitems.itemid(+);
回答by APC
Just for the heck of it, a way of doing it without analytics.
只是为了它，一种无需分析的方法。
Jeffrey's solution needed a DISTINCT because of the duplication of areas. The allitemstable is actually an intersection table between currentitemsand a putative areastable. In the following query this is represented by the inline view ai. There is another inline view totwhich gives us the total number number of records in allitems. This count has to be included in the GROUP BY clause, as it is not an aggregating projector.
Jeffrey 的解决方案需要 DISTINCT，因为areas. 该allitems表实际上是与currentitems推定areas表之间的交表。在以下查询中，这由内联视图表示ai。还有另一个内联视图tot，它为我们提供了allitems. 此计数必须包含在 GROUP BY 子句中，因为它不是聚合投影仪。
SQL> select ai.areas
2 , (count(currentitems.itemid)/tot.cnt) * 100 as "%"
3 from
4 ( select count(*) as cnt from allitems ) tot
5 , ( select distinct areas as areas from allitems ) ai
6 , currentitems
7 , allitems
8 where allitems.areas = ai.areas
9 and allitems.itemid = currentitems.itemid(+)
10 group by ai.areas, tot.cnt
11 /
AREAS %
-------------------- ----------
east 50
south 25
west 0
SQL>
I don't know whether this approach would perform better than Jeffrey's solution: it quite possibly will perform worse (the analytics query certainly has fewer consistent gets). It is interesting simply because it highlights the issues more clearly.
我不知道这种方法是否会比 Jeffrey 的解决方案表现得更好：它很可能会表现得更糟（分析查询肯定有更少的一致获取）。这很有趣，因为它更清楚地突出了问题。
回答by Doug Porter
Here is a quick first pass:
这是一个快速的第一遍：
select ai.areas,
(sum(cnt) / max(tot)) * 100 "Percentage Result"
from (select ai.itemid,
ai.areas,
count(ci.itemid) cnt,
count(ai.areas) over () tot
from allitems ai
left outer join
currentitems ci on (ai.itemid = ci.itemid)
group by ai.itemid, ai.areas
) ai
group by ai.areas
Also, in your test data your itemid 4 needs to be changed to west.
此外，在您的测试数据中，您的 itemid 4 需要更改为 west。
回答by Lluis Martinez
A slight modification of your original query :
对原始查询稍作修改：
Select
areas,
(
Select
Count(*)
From
Allitems Inner_Allitems Left Join Currentitems On (Currentitems.Itemid = Inner_Allitems.Itemid)
Where inner_allitems.areas = outer_allitems.areas
) *100 / (Select Count(*) From allitems ) as percentage
From allitems outer_allitems
Group By areas
回答by Md Aktaruzzaman
Use Group 'By clause':
使用组“按子句”：
Select department_id, min(salary)
From employees
Group By department_id
Having min(salary) >
(
Select min(salary)
From employees
Where department_id <> 50
);
