Results 1 to 4 of 4

Thread: Group by

  1. #1
    Join Date
    Nov 2012
    Posts
    7

    Group by

    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;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,681

    Cool

    Quote Originally Posted by himeda.2000 View Post
    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
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2012
    Posts
    7
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •