How to resolve ORA-00937: not a single-group group function when calculating percentage?
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.