Count of multiple fields

[Phailak]

Hail,

I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

 

[guelphdad]

Sounds like you have a table design issue. Can you show some actual sample rows of your data?

 

[Phailak]

Look at it this way, let's say we have player ID 25 and player ID 32 on the same team. ID 25 scored two goals and assisted on a third let's say and obviously they both have a game played so the data would look like

GameID GP G A Period Time
1 25 0 0 0 null
1 32 0 0 0 null
1 0 25 0 1 12:25
1 0 25 32 2 08:20
1 0 32 25 3 01:35

So I want as a result to show:

PlayerID GP G A
25 1 2 1
32 1 1 1

That make sense? Right now, I'm basically creating a temp table and looping through each player to query each stats (so when I get to player ID 25, I do one query to count GP, then another to count G and another for A, etc and store it). This gets extremely heavy now that we have over 200 players...

 

[guelphdad]

I think you should take the time to normalize your data and split across multiple tables something like:

table: players
playerid,
teamid,
first_name,
last_name

table: gamesplayed
gameid,
playerid

into that table insert each gameid and each player id who has played in a game.

table: game
gameid,
goalid,
period,
timescored

table: playerpoints
gameid,
goalid,
playerid,
goalorassist

the last item just mark as G or A1 or A2 (for assist 1 and assist 2 if those are relevant to you).

There will be a bit more work setting up the tables rather than a single table. You will also have to change any scripts you have to enter the data into the table.

What you wont be doing is storing irrelevant data in your tables.

With the scheme you have now how do you recognize the other 30+ players who played in the game? Do you put them under GP and leave all other data null?

As you also see right now tallying goals and assists per player per game is not very intuitive.

Once you have a set up like the four table system it will be easier to keep track of the information and significantly faster to get the data out. Simple joins will give you info that you are looking for.

 

[Phailak]

Yeah, it makes sense, I never took the time to analyse it before since it came to me that way, I guess it's worth the work to seperate the table and rewrite the scripts...
I'll give it a try to see how well it works! Thanks for the advice