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.

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 …

## All 3 Replies

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

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.

``````teams (team_id, team_name)