Results 1 to 15 of 15

Thread: New to SQL

  1. #1
    Join Date
    Sep 2004
    Posts
    21

    Unanswered: New to SQL

    I'm learning SQL and am trying to solve this problem. I have a table of Teams with a TeamID and another table of people with a TeamID col. I am trying to count the number of people on a team and display the team with the most people.

    I have used this to choose the number of people on the teams:

    SELECT tname, COUNT(tid)
    FROM teams t, people p
    WHERE t.tid=p.tid
    GROUP BY tname;

    this displays the team name and the # of members

    How can I choose the team with the most members?

    Could I do something such as (the syntax may not be correct, but is the overall idea?):

    SELECT tname, count(*)
    FROM tname
    GROUP BY tname
    HAVING count(*) >= all
    (SELECT tname, COUNT(tid)
    FROM teams t, people p
    WHERE t.tid=p.tid)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    SELECT t.tname, count(*)
    FROM teams t, people p
    GROUP BY t.tname
    HAVING count(*) >= ALL
    ( SELECT COUNT(*)
      FROM people p
      GROUP BY p.tid
    )

  3. #3
    Join Date
    Sep 2004
    Posts
    21
    no that printed out the team names and 23 for each team. 23 is the number of rows in people

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's because I forgot the join:
    Code:
    SELECT t.tname, count(*)
    FROM teams t, people p
    WHERE p.tid = t.tid
    GROUP BY t.tname
    HAVING count(*) >= ALL
    ( SELECT COUNT(*)
      FROM people p
      GROUP BY p.tid
    )

  5. #5
    Join Date
    Sep 2004
    Posts
    21
    thanks that worked... Maybe someone could explain how to think through a query in order to solve it...I have programmed C++ which is a procedural, and SQL is not. I'm having trouble relating a question given in english to how it should be written in SQL

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it does require a different frame of mind. I'll try to explain how I got to it...

    The requirement is "display the team with the most people". To find out the number of people in each team we need to look at the people table:

    SELECT p.tid, count(*)
    FROM people p
    GROUP BY p.tid;

    But from those results we only want that group having (hint) the highest count:

    SELECT p.tid, count(*)
    FROM people p
    GROUP BY p.tid
    HAVING count(*) >= ALL (<counts by p.tid>;

    Now this query serves for <counts by p.tid>:

    SELECT COUNT(*)
    FROM people p
    GROUP BY p.tid;

    It will return a list of counts like:
    11
    7
    13
    2

    So we now have:

    Code:
    SELECT p.tid, count(*)
    FROM people p
    GROUP BY p.tid
    HAVING count(*) >= ALL
    ( SELECT COUNT(*)
      FROM people p
      GROUP BY p.tid
    );
    The final part is more or less cosmetic: show the team name instead of the tid. We do that by joining to the team table in the main query, and then grouping by the team name instead of the tid (since, I assumed, both are unique within the teams table). That gives us the final query:
    Code:
    SELECT t.tname, count(*)
    FROM teams t, people p
    WHERE p.tid = t.tid
    GROUP BY t.tname
    HAVING count(*) >= ALL
    ( SELECT COUNT(*)
      FROM people p
      GROUP BY p.tid
    );
    There is more than one way to do it, and it is really a matter of experience and practice to become proficient at solving such problems.

  7. #7
    Join Date
    Sep 2004
    Posts
    21
    Thank you. That was explained well.

  8. #8
    Join Date
    Sep 2004
    Posts
    21
    Suppose I wanted to see which teams had 4 or more. I tried to change all to 4, which worked, but when I tried to output the names of people, nothing was displayed. I assume it because this column is removed some where in the having clause, but how do I retain that information...once again, thanks for taking the time to write the last reply!

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Well you should have had :

    Code:
    SELECT t.tname, count(*)
    FROM teams t, people p
    WHERE p.tid = t.tid
    GROUP BY t.tname
    HAVING count(*) >=4;
    So you display team names and their number of players for teams having at least 4 players.

    Now, I don't understand why you speak of people name. You won't have them with this query.

    What do you exactly want ?

    Regards,

    RBARAER

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Following on from RBARAER's query, you can get all the people's names like this:
    Code:
    SELECT t.tname, p.pname
    FROM teams t, people p
    WHERE p.tid = t.tid
    AND p.tid IN
    ( SELECT p.tid, count(*)
      FROM people p
      GROUP BY p.tid
      HAVING count(*) >=4
    )
    ORDER BY t.tname, p.pname;

  11. #11
    Join Date
    Sep 2004
    Posts
    21
    I'm sorry, when I said people name, it is actually fname and lname. when i tried the code it gave me an error

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    why does it not let me enter p.lname and p.fname in the outer select?

  12. #12
    Join Date
    Sep 2004
    Posts
    21
    NO WAIT...it wasnt the outer select, it was the inner...it does not allow p.tid in the inner select statement. I removed it and it did not produce the correct answer. It returned a group name with 4 members and a gname with 1 member

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Then:
    Code:
    SELECT t.tname, p.fname, p.lname
    FROM teams t, people p
    WHERE p.tid = t.tid
    AND p.tid IN
    ( SELECT p.tid
      FROM people p
      GROUP BY p.tid
      HAVING count(*) >=4
    )
    ORDER BY t.tname, p.pname;
    If that doesn't work, please post the exact SQL and error code/message.

    (I have removed the COUNT(*) that I accidently left in before.)

  14. #14
    Join Date
    Sep 2004
    Posts
    21
    perfect!! How does it count the number of members without actually using count?

  15. #15
    Join Date
    Sep 2004
    Posts
    21
    oops...sorry, didnt notice it in the having

Posting Permissions

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