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

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, i开发者_JS百科t works fine. How do I fix this in Oracle?

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

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 allitems table is actually an intersection table between currentitems and a putative areas table. In the following query this is represented by the inline view ai. There is another inline view tot which 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.

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.

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.

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

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

I guess even this helps:

SELECT
    distinct areas,
    NVL(x.count_item * 100,0) AS Percentage
FROM
    allitems a
    LEFT OUTER JOIN (
        SELECT
            ( COUNT(a.itemid) / (
                SELECT
                    COUNT(*)
                FROM
                    allitems
            ) ) AS count_item,
            areas AS avl_areas
        FROM
            allitems       a
            INNER JOIN currentitems   c ON a.itemid = c.itemid
        GROUP BY
            areas
    ) x ON x.avl_areas = a.areas
;

A workaround that I have found to work (though I think there's a bug here) is this:

-- Doesn't work (though it really should):
select 
  count(*),
  (select count(*) from dual)
from dual;

-- Works
select 
  count(*),
  (select count(*) from dual)
from dual
group by grouping sets (());

dbfiddle