Results 1 to 3 of 3
  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
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There are a number of ways to get the info you are after. You could go with your query and put an order by descending on your count column and only fetch the first record. You could use a sub-query that performs a count and join that to a nested expression to get the one with the highest count. Keep playing around with it and if you have problems, post them here for some tips on how to correct them.
    Dave Nance

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dav1mo View Post
    You could go with your query and put an order by descending on your count column and only fetch the first record.
    that doesn't produce the correct result in case of ties

    the homework question is pretty clear: "If there is more than one project that has attained that maximum, list them all."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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