ORA-00979: not a GROUP BY expression when using join on subquery
This problem grappled me for hours where individual queries were just running good but when using them as table representation in a join I was getting notorious "ORA-00979: not a GROUP BY expression" After sometime I found that i need to use aggregate function in simple select too. Following are individual pieces of query. Query as Table 1: SELECT calcdate, count ( * ) as nodecount FROM ( SELECT TRUNC(lastoccurrence) AS calcdate FROM reporter_status WHERE lastoccurrence < trunc(sysdate) and lastoccurrence > trunc(sysdate) - 2 GROUP BY TRUNC(lastoccurrence),node ) GROUP BY calcdate Query as Table 2: SELECT calcdate, RTRIM (XMLAGG (XMLELEMENT (e, node || ',' )).EXTRACT ( '//text()' ), ',' ) nodes FROM ( SELECT calcdate1 as calcdate,node,RANK FROM ( SELECT TRUNC(lastoccurrence) AS calcdate1,node || ':' || COUNT ( * ) AS node, COUNT ( * ) as counts, RANK() OVER ( PARTITION BY TRUNC(lastoccurrence) OR...