Group by

himeda.2000 · #1 (**permalink**) 12-06-12, 06:03

Hello guys;
I faced one problem while reading about Group Functions; the point says like this...

If an item, which is not a group function, appears in the SELECT list and there is no GROUP BY clause, an “ORA-00937: not a single-group group function” error is raised.

What does this mean? Is that true or error statement?

Thank you;

LKBrwn_DBA · #2 (**permalink**) 12-06-12, 11:52

Quote:

Originally Posted by himeda.2000

What does this mean? Is that true or error statement?

True....

Code:

SQL> SELECT job, SUM (sal)
  2*   FROM scott.emp
SQL> /
SELECT job, SUM (sal)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Above query is missing the "GROUP BY job" clause:

Code:

SQL> SELECT job, SUM (sal)
  2    FROM scott.emp
  3*  GROUP BY job
SQL> /

JOB                           SUM(SAL)
--------------------------- ----------
MANAGER                           8275
ANALYST                           6000
PRESIDENT                         5000
SALESMAN                          5600
CLERK                             4150

himeda.2000 · #3 (**permalink**) 12-07-12, 00:58

Don't you think the following sql statement works correctly??

SQL> SELECT job
2 FROM scott.emp
SQL> /

If an item, which is not a group function, appears in the SELECT list and there is no GROUP BY clause, an “ORA-00937: not a single-group group function” error is raised.

Here the item which is not a group function is "JOB" and it already exist in the SELECT statement and also we don't apply any GROUP BY clause here...but i can't see any error in my compiler.

Littlefoot · #4 (**permalink**) 12-07-12, 03:28

Statement you posted works fine, of course - why wouldn't it? But the result is different if you use GROUP BY.

Jobs without GROUP BY:

Code:

SQL> select job
  2  from emp
  3  order by job;

JOB
---------
ANALYST
ANALYST
CLERK
CLERK
CLERK
CLERK
MANAGER
MANAGER
MANAGER
PRESIDENT
SALESMAN
SALESMAN
SALESMAN
SALESMAN

14 rows selected.

Jobs WITH GROUP BY:

Code:

SQL> select job
  2  from emp
  3  group by job
  4  order by job;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

Now, add another column - department number:

Code:

SQL> select deptno, job
  2  from emp
  3  order by deptno, job;

    DEPTNO JOB
---------- ---------
        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 ANALYST
        20 ANALYST
        20 CLERK
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN
        30 SALESMAN
        30 SALESMAN
        30 SALESMAN

14 rows selected.

Now, group result set by department number:

Code:

SQL> select deptno, job
  2  from emp
  3  group by deptno
  4  order by deptno, job;
select deptno, job
               *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Of course you can't do that - if you are grouping by DEPTNO, you have to group by JOB as well because it is in the SELECT column list (that's what you read in documentation). So:

Code:

SQL> select deptno, job
  2  from emp
  3  group by deptno, job
  4  order by deptno, job;

    DEPTNO JOB
---------- ---------
        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 ANALYST
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN

9 rows selected.

Also, GROUP BY is necessary when you use aggregate functions. You don't need it here:

Code:

SQL> select count(*)
  2  from emp;

  COUNT(*)
----------
        14

But you need it here:

Code:

SQL> select deptno, count(*)
  2  from emp
  3  order by deptno;
select deptno, count(*)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Code:

SQL> select deptno, count(*)
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6

Internet Services

Web Development

Digital Marketing

Consumer Tech