Using MAX() with COUNT() and comparing

msb0dg3r · #1 (**permalink**) 07-21-10, 17:41

I'm currently leaning sql, and while waiting for my oracle account to be set up, I've been doddling about using mysql.

Retrieve the name and number of the project which uses the most employees. Also show the total number of employees for that project. If there is more than one project that has attained that maximum, list them all.

The tables(with columns) are as follows:

Project (Pname, Pnumber, Dnum)

'ProductX', 1, 5
'ProductY', 2, 5
'ProductZ', 3, 5
'Computerization', 10, 4
'Reorganization', 20, 1
'Newbenefits', 30, 4

Works_On (Essn, Pno)

'123456789', 1
'123456789', 2
'333445555', 1
'333445555', 2
'333445555', 3
'333445555', 10
'333445555', 20
'333445555', 30
'453453453', 1
'453453453', 2
'666884444', 1
'666884444', 3
'888665555', 20
'987654321', 20
'987654321', 30
'987987987', 10
'987987987', 30
'999887777', 10
'999887777', 30

I used the following code to find the total number of employees that work on each project.

select pname, pnumber, count(essn) as e_total
from project, works_on
where pno = pnumber
group by pnumber;

'ProductX', 1, 4
'ProductY', 2, 3
'ProductZ', 3, 2
'Computerization', 10, 3
'Reorganization', 20, 3
'Newbenefits', 30, 4

The problem is now displaying the row(s) that have the maximum employees. I've tried a handful of different techniques I found on forums...but nothing would work. Since I am new to this, I'm having a hard time knowing all the rules for subqueries, etc. I also found you can't do max(count(*)). Thanks for any help!

anacedent · #2 (**permalink**) 07-21-10, 18:10

>I also found you can't do max(count(*))
REALLY?

Code:

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

MAX(COUNT(*))
-------------
	    9

msb0dg3r · #3 (**permalink**) 07-21-10, 18:37

Well, at least in mysql you can't. I haven't been able to actually do anything using my oracle account yet.

anacedent · #4 (**permalink**) 07-21-10, 18:46

some/many/most folks just install Oracle RDBMS on their PC to have access to a sandbox environment

reddy_546 · #5 (**permalink**) 07-22-10, 05:08

Try using below query.

SELECT pname,pnumber,e_total from
(select pname, pnumber, count(essn) as e_total
from project, works_on
where pno = pnumber
group by pnumber) P
WHERE P.e_total = max(P.e_total)

msb0dg3r · #6 (**permalink**) 07-22-10, 10:02

Quote:

Originally Posted by reddy_546

Try using below query.

SELECT pname,pnumber,e_total from
(select pname, pnumber, count(essn) as e_total
from project, works_on
where pno = pnumber
group by pnumber) P
WHERE P.e_total = max(P.e_total)

Thank you! This definitely makes sense to me...it's similar to what I've been trying...I gave this a shot in mysql and it didn't work. I downloaded Oracle Database 10g Express Edition but do not have a system password that was given during installation (?)...so, I'm having a problem testing all of this in an Oracle environment. Do you have any suggestions?

r937 · #7 (**permalink**) 07-22-10, 10:57

Quote:

Originally Posted by msb0dg3r

I gave this a shot in mysql and it didn't work.

and no wonder

that WHERE clause with P.e_total = max(P.e_total) smells worse than ten day old chicken thighs left out on the kitchen counter

i'm no oracle guy but i would be surprised if it actually works in oracle

msb0dg3r · #8 (**permalink**) 07-22-10, 15:11

Well, I've now been using Oracle...and tried this

WITH
count_emp AS
(SELECT pno, count(essn) as cnt
FROM works_on
GROUP BY pno)
SELECT pname, cnt
FROM project, count_emp, works_on
WHERE cnt = (SELECT max(cnt) FROM count_emp)
GROUP BY pname, cnt;

however, it gives me these results:

PNAME CNT
Computerization 4
ProductX 4
Newbenefits 4
Reorganization 4
ProductZ 4
ProductY 4

When, I'm trying to only get the rows that have the maximum amount of employees. This is just replacing all cnt of employees to the max.
Any help!?

Internet Services

Web Development

Digital Marketing

Consumer Tech