Help with subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with subquery

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2005-05-22 : 22:26:14

Hello,

I have a simple table that has team information:

Teams
-----
TeamId
TeamName
TeamManager

I have a query that returns all that info:

SELECT TeamId, TeamName, TeamManager FROM TEAMS

In addition, there is a players table. Each player has a "teamid" field associated with them. In the query above, I also want to return how many players are in each team. I cannot figure out the correct way to do this. I have tried this below: (but I know this is not correct)

SELECT TeamId,
(SELECT COUNT(teamid) FROM Players GROUP BY teamid),
TeamName,
TeamManager
FROM TEAMS

Any pointers on this one?

Thanks
Danny

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-22 : 22:44:26

try exploring Joins and Group by clause,then post whatever you have if you still encounter a problem

-- check BOL for syntax

HTH

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-22 : 22:49:58

You're close but the subquery needs to return a single value - and just the count for the team in that row.

SELECT TeamId,
(SELECT COUNT(*) FROM Players p where p.TeamId = t.TeamId),
TeamName,
TeamManager
FROM TEAMS t

also
SELECT TeamId,
COUNT(*) ,
max(TeamName),
max(TeamManager)
FROM TEAMS t
left join Players p
on p.TeamId = t.TeamId
group by p.TeamId

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dcarva
Posting Yak Master

140 Posts

Posted - 2005-05-22 : 23:04:52

Thanks to both of you for your help.

This worked:

SELECT TeamId,
(SELECT COUNT(*) FROM Players p where p.TeamId = t.TeamId),
TeamName,
TeamManager
FROM TEAMS t

I was pretty close I guess.

Thanks
Danny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources