OBIEE - ORA-00937 - Not a single-group group function

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

You may receive this error and below are some causes.

Odbc driver returned an error (SQLExecDirectW).
  Error Details
State: HY000. Code: 2057371920. [NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 17001] Oracle Error code: 937, message: ORA-00937: not a single-group group function at OCI call OCIStmtExecute. 
[nQSError: 17010] SQL statement preparation failed. (HY000)

3 - Solution

3.1 - Bad aggregation rule

On one percentage measure was the aggregation rule of answer set to default. By setting it on Average the problem was resolved.


3.1.1 - By statement not completely defined

To get the grand total of a column, you may use the by syntax:

avg(MyFact.My Measure BY)

In combination with the filter function, this may bring problems then just define the by statement completely by adding the group by columns such as:

avg(MyFact.My Measure BY MyDimension.MyAttribute1, MyDimension.MyAttribute2)