If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Group by

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,657
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,985
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On