How to resolve ORA-00937: not a single-group group function when calculating percentage?

ora-00937, oracle, sql

Solution

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(+);

Problem

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` 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: ``` 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?

Original source