Home > Database, Oracle, Uncategorized > Grab Oracle/PLSQL Errors – Code: ORA-00979

Grab Oracle/PLSQL Errors – Code: ORA-00979

Error Message:

ORA-00979: not a GROUP BY expression

Cause of Error:

The GROUP BY clause does not contain all the expressions in the SELECT clause.

Explanation:

SELECT expressions that are not included in a group function, like AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause. Otherwise this error will occur. Actually it occurs when someone tries to execute a SELECT statement that includes a GROUP BY function (ie: MINMAXSUMCOUNT) and an expression in the SELECT list, that was not in the GROUP BY clause.

Possible Resolution:

  1. Rewriting the SELECT statement so that the column or expression listed in the SELECT list is also found in the GROUP BY clause. But in that don’t include the SELECT expressions that are arguments of group functions.
  2. Removing the GROUP BY function (ie: MINMAXSUMCOUNT) from the SELECT statement.
  3. Remove the expression from the SELECT list that was not in the GROUP BY clause.

Example:

If the following query is being executed:

SQL> select client, segment, count(*)
2 from corporate_segment
3 group by client;
select client, segment, count(*)
*

The following error occurs:

ERROR at line 1:
ORA-00979: not a GROUP BY expression

This happens because here segment “expression” is also selected  along with “client” but “segment” is not a group function argument and not includded in the GROUP BY clause.

So the current one should be:

SQL> select client, segment, count(*)
2 from corporate_segment
3 group by client, segment
CLENT                             SEGMENT             COUNT(*)

WHS Group                   Corp1                           17

Xmacz Inc.                     Corp2                           27

Check out for more similar “ORA – *” errors later in the series. For now its adios🙂

  1. Munir
    May 22, 2010 at 2:19 pm

    Nice, Thank you.
    Boss, I will suggest you to use “TOAD for ORACLE” rather than *isqlplus

  2. May 22, 2010 at 3:27 pm

    thanks a lot Munir🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: