StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to resolve ORA-00937: not a single-group group function when calculating percentage?
text
Body
copied!<p>I'm trying to get a percentage of the itemid that are available in a certain area. Using my query, I get an error <code>ORA-00937: not a single-group group function</code></p> <p>All the details:</p> <p>I have these two tables:</p> <pre><code>ALLITEMS --------------- ItemId | Areas --------------- 1 | EAST 2 | EAST 3 | SOUTH 4 | WEST CURRENTITEMS --------------- ItemId --------------- 1 2 3 </code></pre> <p>and want this result:</p> <pre><code>--------------- 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 </code></pre> <p>The DDL:</p> <pre><code>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); </code></pre> <p>My Query:</p> <pre><code>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 </code></pre> <p>The error:</p> <pre><code>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 </code></pre> <p>When I run the exact same query in SQL Server, it works fine. How do I fix this in Oracle?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload