New to SQL

rmill9681 · #1 (**permalink**) 10-03-04, 19:32

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)

andrewst · #2 (**permalink**) 10-04-04, 07:09

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
)

rmill9681 · #3 (**permalink**) 10-04-04, 12:57

no that printed out the team names and 23 for each team. 23 is the number of rows in people

andrewst · #4 (**permalink**) 10-04-04, 13:03

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
)

rmill9681 · #5 (**permalink**) 10-04-04, 13:17

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

andrewst · #6 (**permalink**) 10-04-04, 14:21

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.

rmill9681 · #7 (**permalink**) 10-04-04, 14:28

Thank you. That was explained well.

rmill9681 · #8 (**permalink**) 10-04-04, 14:50

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!

RBARAER · #9 (**permalink**) 10-05-04, 07:55

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

andrewst · #10 (**permalink**) 10-05-04, 08:09

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;

rmill9681 · #11 (**permalink**) 10-05-04, 08:51

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?

rmill9681 · #12 (**permalink**) 10-05-04, 08:55

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

andrewst · #13 (**permalink**) 10-05-04, 08:58

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.)