Results 1 to 6 of 6

Thread: SQL Help

  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Question Unanswered: SQL Help

    Hello,

    I am fairly new to SQL and I need some help (quite desperately) with a query that I wrote to retrieve the names of employees who work on more than one project.

    I have attached a schema diagram to make things a little clearer.

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

    This is the query (I wrote) that is in doubt:

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

    Please let me know what you think.

    Thank you very much indeed.

    Sincerely,
    c360
    Last edited by Centro360; 04-07-04 at 22:27.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    HAVING comes after GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    13
    Hello r937,

    Thank you for your reply.

    Would my query work if it is now:

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

    Please offer me your comments.

    Thank you,
    c360

    P.S. Have you seen the schema diagram?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes and yes

    :-)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Posts
    13

    Thumbs up

    Thanks a lot, r937.


    c360

  6. #6
    Join Date
    Apr 2004
    Posts
    13

    Question Using MAX on COUNT

    I wrote another query which requires me to find the names of employees who work on the most projects.

    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

Posting Permissions

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