Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Unanswered: Using MAX on COUNT

    Hello,

    I need some help with a query I wrote which requires me to find the names of employees who work on the most projects.

    This is the URL for the schema diagram:

    http://www.cs.ucl.ac.uk/staff/j.lewi..._employ_er.gif

    I was advised to use the MAX function on the COUNT function, but I do not know how that works but I made an attempt anyhow.

    Here are 2 queries I wrote:

    without MAX

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    ORDER BY COUNT(e.ptitle);

    and using MAX on COUNT,

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    AND e.ptitle IN
    (SELECT MAX(e.ptitle)
    FROM emp_proj e) IN
    (SELECT COUNT(e.ptitle)
    FROM emp_proj e);

    // This query most probably doesn't make sense.

    Please provide me with some feedback, and in particular, how MAX can be used on COUNT.

    Thank you very much indeed.

    c360

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Why not just add DESC in your ORDER BY clause:

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    ORDER BY COUNT(e.ptitle) DESC;

    It should show the employees at the top who work on the most projects.
    -cf

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    select from (
    select max(project_countover () max_projectsp.* from (
    select 
    count
    (e.ptitleover (partition by p.pnameproject_countp.*
    from person pemp_proj e
    where p
    .pnum e.pnump)
    where max_projects project_count
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2004
    Posts
    13
    Hi chuck_forbes,

    Is there any way I could project the tuple that shows the name of the employee with the highest salary?

    Thanks.

    c360

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there any way I could project the tuple that shows the name of the employee with the highest salary?

    Yes, many ways using the MAX function in some manner, shape or form.
    What have you tried so far?
    Need a free clue? go to
    http://asktom.oracle.com
    and then do a keywork search on your current homework problem
    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.

  6. #6
    Join Date
    Apr 2004
    Posts
    13
    Hello anacedent,

    I have written 2 queries (one without using MAX and another is an attempt using MAX on COUNT):

    Without MAX:

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    ORDER BY COUNT(e.ptitle);

    Using MAX on COUNT:

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    AND e.ptitle IN
    (SELECT MAX(e.ptitle)
    FROM emp_proj e) IN
    (SELECT COUNT(e.ptitle)
    FROM emp_proj e);

    ------------------------------------------------------------------------------------

    I have had one reply from chuck_forbes suggesting that I use DESC like this:

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    ORDER BY COUNT(e.ptitle) DESC;

    // I am perfectly fine with this, but it would have been better if I could just project the tuple showing only the name of the employee with the highest salary.

    Another reply from The_Duck suggested another more complex query, using MAX on COUNT, that I do not understand at all:

    select * from (
    select max(project_count) over () max_projects, p.* from (
    select
    count(e.ptitle) over (partition by p.pname) project_count, p.*
    from person p, emp_proj e
    where p.pnum = e.pnum) p)
    where max_projects = project_count;

    ------------------------------------------------------------------------------------

    I would like to know whether the query chuck_forbes wrote can be modified to project the tuple with the name of the employee with the highest salary instead of showing the entire list arranged in DESC order.

    Thanks a lot.

    c360

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Hope this works:
    SELECT ROWNUM as Rank, p.pname, count(e.ptitle)
    FROM
    (SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    ORDER BY COUNT(e.ptitle) DESC)
    WHERE ROWNUM <= 3;

    This is how Top N analysis works. Hope this works for you

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Perhaps:

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    GROUP BY p.pname
    HAVING COUNT(e.ptitle) = MAX(COUNT(e.ptitle));

    Haven't tried it, but it might work.

    -Chuck

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    what's wrong with the query I gave you????
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    Originally posted by chuck_forbes
    Perhaps:

    SELECT p.pname, COUNT(e.ptitle) AS "project count"
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    GROUP BY p.pname
    HAVING COUNT(e.ptitle) = MAX(COUNT(e.ptitle));

    Haven't tried it, but it might work.

    -Chuck
    Oops, doesn't work in Oracle, but this should:

    SELECT p.pname, COUNT(*) prj_cnt
    FROM person p, emp_proj e
    WHERE p.pnum = e.pnum
    GROUP BY p.pname
    HAVING COUNT(*) = (SELECT MAX(iv.cnt)
    FROM (SELECT count(*) cnt
    FROM person p2, emp_proj e2
    WHERE p2.pnum = e2.pnum
    GROUP BY p.pname) iv)
    -cf

Posting Permissions

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