Count Non-Zero Columns In Record

[jshenn]

A table contains results of a match. Each match is a series of 7-9 games (different leagues share same match schedule table and play different best of "x" matches), such as:

id
teamA
game1
game2
.
.
.
game9

Need to calculate average based on game total and number of games played. Since I can't exactly tell how many games should have results for a given match, I would like to count the number of games whose value is >0. I know I can do this using php and looping through the record, but it would be better to create either a trigger or scheduled event.

[smantscheff]

First of all, this looks like bad table design. A table with similar columns most probably should be split in at least two tables.
Second, you don't tell where your problem is. To count non-zero column values in a stored procedure or function this function will have to contain an expression like

if(game1 is not null, 1, 0) 
+ if(game2 is not null, 1, 0)
...
+ if(game9 is not null, 1, 0)

[jshenn]

On the table design, this is a table of game results.

Id is the match id (unique). For each match there are up to 9 games. In each gamex column, the score of that game is entered. So a complete record would have a match id, the id of the team entering scores, and then between 5 and 9 of the gamex columns filled in with an integer value between 0 and 300. There is a corresponding record for the opposing team where they enter their scores.

How would you propose splitting that data into a better db design?

Gamex fields start as null and are updated to an integer between 0 and 300 by another procedure. Ideally a trigger on the table would update a game_count field with the count of non-null gamex columns in that record. My problem was that I do not know sql sp well enough to even get started. I know how to do this as part of a procedure in javascript or php, but I would much rather have this update server-side.

Your answer probably gets me started, but I am going leave the thread as unanswered in case others have suggestions.

[smantscheff]

Your design is definitively flawed as one information - the outcome of a game - is stored in two locations: with each team separately.
I'd propose a structure like:

teams (team_id, team_name)
games (game_id, team_1, team_2, game_result)

With this structure it is much easier to count games, to avoid duplicates or to calculate averages.