Hi everyone,
I was hoping to see if anyone could provide an explanation on why this error is happening. I am encountering an "ORA-00937: not a single-group group function".
I am going to demonstrate the error using the dual table.
This SQL statement below runs without error:
SELECT
COUNT(*) OVER(PARTITION BY T.DUMMY) AS RC,
DUMMY,
COUNT(*)
FROM DUAL T
GROUP BY DUMMY
However, when I put it in an inline view with a join I get the error I mentioned above.
SELECT T4.* FROM
DUAL T4
INNER JOIN
(
SELECT
COUNT(*) OVER(PARTITION BY T.DUMMY) AS RC,
DUMMY,
COUNT(*)
FROM DUAL T
GROUP BY DUMMY
) T3
ON T4.DUMMY = T3.DUMMY
I was curious and changed the INNER JOIN to a LEFT JOIN. Now the error doesn't happen.
SELECT T4.* FROM
DUAL T4
LEFT JOIN
(
SELECT
COUNT(*) OVER(PARTITION BY T.DUMMY) AS RC,
DUMMY,
COUNT(*)
FROM DUAL T
GROUP BY DUMMY
) T3
ON T4.DUMMY = T3.DUMMY
The issue seems to be coming from the "COUNT(*) OVER(PARTITION BY T.DUMMY) AS RC", but what exactly is causing this or how do I go about finding out what is causing this?
Thanks for the assistance.