asked on

PL/SQL: ORA-00937 ERROR - How to correct when using aggregate fields

Hello all,

I have the following error:
SQL error. Stmt #: 5653 Error Position: 1720 Return: 937 - ORA-00937: not a single-group group function A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=937, Message=ORA-00937: not a single-group group function (50,380)

I realize that it has something to do with the aggregate functions and grouping, just not sure how to solve it.
Would it be better if I rewrote some of the expressions?
Listed below is the SQL

SELECT
SUM (CASE WHEN ( A.ERROR_ID = ERROR_1 ) OR ( A.ERROR_ID = ERROR_2') THEN 1 END) ,

(SUM (CASE WHEN ( A.ERROR_ID = ERROR_1 ) OR ( A.ERROR_ID = ERROR_2') THEN 1 END) / count ( A.ERROR_ID) ) * 100,

SUM (CASE WHEN ( A.ERROR_ID = ERROR_3 ) OR ( A.ERROR_ID = ERROR_4) THEN 1 END ),

(SUM (CASE WHEN ( A.ERROR_ID = ERROR_3) OR ( A.ERROR_ID = ERROR_4) THEN 1 END) /COUNT( A.ERROR_ID)) * 100,

SUM( CASE WHEN ( A.ERROR_ID != ERROR_1) AND ( A.ERROR_ID != ERROR_2') AND ( A.ERROR_ID != ERROR_3) AND ( A.ERROR_ID != ERROR_4) AND ( A.ERROR_ID != ERROR_5) AND ( A.ERROR_ID != ERROR_8) AND ( A.ERROR_ID != ERROR_6) AND ( A.ERROR_ID != ERROR_7) THEN 1 END ) ,

SUM( CASE WHEN ( A.ERROR_ID != ERROR_1) AND ( A.ERROR_ID != ERROR_2') AND ( A.ERROR_ID != ERROR_3) AND ( A.ERROR_ID != ERROR_4) AND ( A.ERROR_ID != ERROR_5) AND ( A.ERROR_ID != ERROR_8) AND ( A.ERROR_ID != ERROR_6) AND ( A.ERROR_ID != ERROR_7) THEN 1 END ) / COUNT( A.ERROR_ID) * 100,

SUM( ( CASE WHEN ( A.ERROR_ID = ERROR_5) THEN 1 END ) ) ,

(SUM(DECODE( A.ERROR_ID ,ERROR_5,1,0)) * 100 )/ COUNT( A.ERROR_ID) , SUM (CASE WHEN ( A.ERROR_ID = ERROR_8 ) THEN 1 END) ,

(SUM (CASE WHEN ( A.ERROR_ID = ERROR_8) THEN 1 END) / COUNT( A.ERROR_ID) ) * 100, SUM (CASE WHEN ( A.ERROR_ID = ERROR_6 ) OR ( A.ERROR_ID = ERROR_7) THEN 1 END) ,

SUM (CASE WHEN ( A.ERROR_ID = ERROR_6 ) OR ( A.ERROR_ID = ERROR_7) THEN 1 END) * 100 / count ( A.ERROR_ID),
B. Prompt
FROM Table A, Table B
WHERE B.User = XYZ
AND ( B. Prompt = :1 )

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

syhctl

ASKER

The (B.Prompt = :1) indicates group by but the SQL does not say that. I'll check on that.

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sonicefu

Problem:
ORA-00937: not a single-group group function

Cause:
You tried to execute a SELECT statement that included a GROUP BY function (ie: MIN, MAX, SUM, COUNT), but was missing the GROUP BY clause.

Action:
The options to resolve this Oracle error are:
1. Rewrite the SELECT statement so that the column or expression listed in the SELECT list is also
found in the GROUP BY clause.
2. Remove the GROUP BY function (ie: MIN, MAX, SUM, COUNT) from the SELECT statement.
3. Remove the expression from the SELECT list that was not in the GROUP BY clause.

For example, if you had tried to execute the following SELECT statement:

SELECT department, MIN(salary) as "Lowest salary"
FROM employees;

You would receive the following error message:
ORA-00937: not a single-group group function

You could correct this by including department in the GROUP BY clause as follows:

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

ASKER CERTIFIED SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

syhctl

ASKER

Thanks to all,
All answers seem to work for this problem, Kamadar's for the system I am using was the simplest fix --- so I will split the points.
Thanks again,
Syhctly