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:
Query as Table 2:
The erroneous sql is as following:
The solution is marked in yellow in the corrected query. The only difference is that I used aggregate function. I still have no clue why it is like that, but at last this works.
"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) ORDER BY count(*) DESC) RANK FROM reporter_status WHERE lastoccurrence < trunc(sysdate) and lastoccurrence > trunc(sysdate)-2 GROUP BY TRUNC(lastoccurrence),node ORDER BY COUNT(*) DESC )WHERE RANK <11 order by calcdate1 ) GROUP BY calcdate
Individually they both run good without error. However I am using XMLAGG instead of LISTAGG as my databases are running on Oracle 11g Release 1.
The erroneous sql is as following:
SELECT tab_total.calcdate AS calcdate, tab_total.nodecount AS nodecount, tab_summary.nodes AS topnodes FROM ( 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 ) tab_total , ( 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) ORDER BY count(*) DESC) RANK FROM reporter_status WHERE lastoccurrence < trunc(sysdate) and lastoccurrence > trunc(sysdate)-2 GROUP BY TRUNC(lastoccurrence),node ORDER BY COUNT(*) DESC )WHERE RANK <11 order by calcdate1 ) GROUP BY calcdate ) tab_summary WHERE tab_total.calcdate=tab_summary.calcdate
The solution is marked in yellow in the corrected query. The only difference is that I used aggregate function. I still have no clue why it is like that, but at last this works.
SELECT tab_total.calcdate AS calcdate, tab_total.nodecount AS nodecount, tab_summary.nodes AS topnodes FROM ( 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 ) tab_total , ( SELECT max(calcdate) as 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) ORDER BY count(*) DESC) RANK FROM reporter_status WHERE lastoccurrence < trunc(sysdate) and lastoccurrence > trunc(sysdate)-2 GROUP BY TRUNC(lastoccurrence),node ORDER BY COUNT(*) DESC )WHERE RANK <11 order by calcdate1 ) GROUP BY calcdate ) tab_summary WHERE tab_total.calcdate=tab_summary.calcdate
Comments
Post a Comment