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

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

HashiCorp Vault Integration with Ansible Etower using approle

utility to extract date from text with java