如何解决ORA-00937:计算百分比时不是单组分组功能? [英] How to resolve ORA-00937: not a single-group group function when calculating percentage?
本文介绍了如何解决ORA-00937:计算百分比时不是单组分组功能?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试获取特定区域中可用的itemid的百分比.
使用查询,我得到一个错误ORA-00937: not a single-group group function
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
所有详细信息:
我有这两个表:
ALLITEMS
---------------
ItemId | Areas
---------------
1 | EAST
2 | EAST
3 | SOUTH
4 | WEST
CURRENTITEMS
---------------
ItemId
---------------
1
2
3
并希望得到此结果:
---------------
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
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);
我的查询:
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
错误:
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
当我在SQL Server中运行完全相同的查询时,它运行良好. 如何在Oracle中解决此问题?
When I run the exact same query in SQL Server, it works fine. How do I fix this in Oracle?
推荐答案
分析是您的朋友:
SELECT DISTINCT
areas
,COUNT(currentitems.itemid)
OVER (PARTITION BY areas) * 100
/ COUNT(*) OVER () Percentage
FROM allitems, currentitems
WHERE allitems.itemid = currentitems.itemid(+);
这篇关于如何解决ORA-00937:计算百分比时不是单组分组功能?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文