Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: Using MAX() with COUNT() and comparing

    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!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2010
    Posts
    5
    Well, at least in mysql you can't. I haven't been able to actually do anything using my oracle account yet.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    some/many/most folks just install Oracle RDBMS on their PC to have access to a sandbox environment
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16
    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)
    -PavanKumar M Reddy

  6. #6
    Join Date
    Jul 2010
    Posts
    5
    Quote Originally Posted by reddy_546 View Post
    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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by msb0dg3r View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2010
    Posts
    5
    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!?

Tags for this Thread

Posting Permissions

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