Ok so I'm working on my homework and am having trouble getting the Count function to work correctly.
I am supposed to use "A query that lists all teams that have more than 4 players (in the players table). Include the columns team_id, team_name, and the count of players for that team."
It is just a simple NHL database our teacher made up for this assignment. There are 18 teams in the teams database and 74 players in the players database.
This is what I have written as of right now:
SELECT teams.team_id, teams.team_name, COUNT(players.team_id) AS PlayerCount
FROM teams, players
GROUP BY teams.team_id, teams.team_name
HAVING COUNT(players.team_id) > 4;
And when I run that this is the output I get:
TEAM_ID TEAM_NAME PLAYERCOUNT
------- ----------------------------------- ----------------------
TBL Tampa Bay Lightening 74
BOS Bostong Bruins 74
SJS San Jose Sharks 74
NYI New York Islanders 74
MIN Minnesota Wild 74
DET Detroit Red Wings 74
NYR New York Rangers 74
PHL Philadelphia Flyers 74
BUF Buffalo Bruins 74
PIT Pittsburgh Penguins 74
DAL Dallas Stars 74
VAN Vancouver Canucks 74
WSH Washington Capitals 74
COL Colorado Avalanche 74
TOR Toronto Maple Leafs 74
CLB Columbus Blue Jackets 74
CHI Chicago Blackhawks 74
ATL Atlanta Thrashers 74
18 rows selected
I know it's probably pretty simple to fix, but I can't find anything that's like that in my textbook and the Google results I get are either not the same thing or more sophisticated than what I am trying to do.
Any help is greatly appreciated.
