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

Popular posts from this blog

Delete horizontal, vertical and angled lines from an image using Python to clear noise and read text with minimum errors

Java Currency Formatter Changing $ to ¤

Caused by: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation