Home » SQL & PL/SQL » SQL & PL/SQL » SQL - max(count(department_id))
SQL - max(count(department_id)) [message #361965] Fri, 28 November 2008 20:57 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
pls go thru the below query..i am trying to get the max count of department_id and last_name..i am not getting if i use inline view and subquery cann't be used in this case.

pls help me...

select last_name
from (select last_name,max((count(department_id))) from employees
group by last_name);

from (select last_name,max((count(department_id))) from employees
*

ERROR at line 2:
ORA-00937: not a single-group group function
Re: SQL - max(count(department_id)) [message #361968 is a reply to message #361965] Fri, 28 November 2008 21:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Please follow the forum guidelines for formatting your post.

Check Aggregate Functions .

[Updated on: Fri, 28 November 2008 21:30]

Report message to a moderator

Re: SQL - max(count(department_id)) [message #361970 is a reply to message #361968] Fri, 28 November 2008 21:39 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
hi..pls provide me the full query.can't i use max(count(department_id)?..pls explain in detail.thnks
Re: SQL - max(count(department_id)) [message #361972 is a reply to message #361970] Sat, 29 November 2008 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide:
- Don't use IM speak
- Format your post
- Post a test case: create table and insert statements along with the result you want with these data.
- You then know that we DON'T provide solution to beginners, just clues and hints

Regards
Michel
Re: SQL - max(count(department_id)) [message #361985 is a reply to message #361970] Sat, 29 November 2008 01:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ygsunilkumar wrote on Sat, 29 November 2008 09:09
hi..pls provide me the full query.can't i use max(count(department_id)?..pls explain in detail.thnks


NO .. you cant.

First of all , you are using aggregate function over another aggregate function which is not allowed for group by.

Secondly , how does it make sense to apply MAX over Count function against the same lastname ?

Smile
Rajuvan
Re: SQL - max(count(department_id)) [message #362010 is a reply to message #361985] Sat, 29 November 2008 19:59 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
ok.Then what should i write to get the output.Because i want to calculate first count of department_id and again max of department_id and get last_name.please help me
Re: SQL - max(count(department_id)) [message #362013 is a reply to message #361985] Sat, 29 November 2008 21:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rajavu1 wrote on Sat, 29 November 2008 18:40
First of all , you are using aggregate function over another aggregate function which is not allowed for group by.


I reckon you can...

select min(max(sal))
from emp
group by deptno

MIN(MAX(SAL)) 
-------------
3000


Ross Leishman
Re: SQL - max(count(department_id)) [message #362017 is a reply to message #362013] Sun, 30 November 2008 00:03 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
i am trying to get maximum count of department_id and last_name,but i am getting error.please suggest me.

select last_name, max(count(department_id))
from employees
group by last_name;
select last_name, max(count(department_id))
*
ERROR at line 1:
ORA-00937: not a single-group group function
Re: SQL - max(count(department_id)) [message #362020 is a reply to message #362017] Sun, 30 November 2008 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 29 November 2008 07:19
Please read OraFAQ Forum Guide:
- Don't use IM speak
- Format your post
- Post a test case: create table and insert statements along with the result you want with these data.
- You then know that we DON'T provide solution to beginners, just clues and hints

Regards
Michel



Use MAX in its analytcal form.

Regards
Michel
Re: SQL - max(count(department_id)) [message #362023 is a reply to message #361965] Sun, 30 November 2008 00:47 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Hi sunil,

what you wants to do .tell me the exact thing .
do u know how group function works.?.

select ename,count(deptno)
from emp
group by ename
/



ENAME COUNT(DEPTNO)
---------- -------------
ADAMS 1
ALLEN 1
BLAKE 1
CLARK 1
FORD 1
JAMES 1
JONES 1
KING 1
MARTIN 1
MILLER 1
SCOTT 1

ENAME COUNT(DEPTNO)
---------- -------------
SMITH 1
TURNER 1
WARD 1


If you wants to find the query with max employees in the deptno then you could do like the following..


 1  select max(count(deptno))
  2  from emp
  3* group by deptno
SQL> /

MAX(COUNT(DEPTNO))
------------------
                 6
 



and you should not use last name column in the select statement it will return an error if you are supposed to. bcos max will return only one value for a particular group.

i guess this might help you..now onwards try to format the code like what i did above.then you will certainly get more responses.

regards
Seyed Mohamed
Re: SQL - max(count(department_id)) [message #362028 is a reply to message #362023] Sun, 30 November 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now onwards try to format the code like what i did above

Also do it for output in order to align result columns and don't use IM speak: bcos -> because

In addition, OP wants last_name in the output.

Regards
Michel
Re: SQL - max(count(department_id)) [message #362032 is a reply to message #362020] Sun, 30 November 2008 01:15 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@seyed456
seyed456 wrote on Sun, 30 November 2008 12:17

you should not use last name column in the select statement it will return an error if you are supposed to. bcos max will return only one value for a particular group.



I hope you didn't notice the following reply:

Michel Cadot wrote on Sun, 30 November 2008 11:50

Use MAX in its analytcal form.
Regards
Michel



Regards,
Jo
Previous Topic: Help regarding how to find length of chain in SQL
Next Topic: SQL based on number of count
Goto Forum:
  


Current Time: Tue Dec 06 16:18:07 CST 2016

Total time taken to generate the page: 0.10644 seconds