# Thread: Sum of values from one table divided by count of values from another?

1. Registered User
Join Date
Feb 2015
Posts
4

## Unanswered: Sum of values from one table divided by count of values from another?

Hopefully this is an easy question for someone who's an expert in mySQL! I'm self-taught in this stuff so I appreciate any help. This one's a little beyond my skills.

I have a database of hockey statistics and I'm looking for a points-per-game report.

In one table, I have a points value by game (Joe Smith of Team A got 4 points on 17 February 2015). Relevant columns are player, date and total_points.

In another table, I have a schedule of games for the entire season, even ones scheduled in the future. The schedule table can be joined to the player table by team name, but the schedule differentiates between home and away team. So to find the games to date you'd have to do something like select count(*) from schedule where date < now and (away = "Team A" or home = "Team A").

I'm looking for a sorted report of points per game by player, defined as the sum of all points the player received to date, divided by the number of games his team has played to date. So if Joe Smith has 10 points to date and has played 5 games he would show up on the report as having 2 points per game.

I can calculate this with some code and brute force, but I'm wondering if there is a way to get this out of mySQL as a query. (No, this is not a homework assignment, either - it's for a website I run as a hobby.)

Thank you!

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
What if the player has only played 4/5 of the teams games; do you still want to divide by 5?

3. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Seems things are being over complicated here.

In one table, I have a points value by game (Joe Smith of Team A got 4 points on 17 February 2015). Relevant columns are player, date and total_points.
Why would you need to go to the schedule table if you aren't differentiating between home/away? Seems all the info you need is in this one table.
something like:
Code:
```select player, count(*), sum(total_points), sum(total_points)/count(*)
from points_value_by_game
group by player
order by player```

4. Registered User
Join Date
Feb 2015
Posts
4
Originally Posted by gvee
What if the player has only played 4/5 of the teams games; do you still want to divide by 5?
Originally Posted by dav1mo
Seems all the info you need is in this one table.
Thank you!

Sorry, the piece I didn't mention is that if the player doesn't accumulate any points, there is no entry in the results table. The schedule is the only way to get quantity of games. So to gvee's point there is no way to know if the player appeared in the game but was scoreless; all I know is if his team played. (I'm limited by the statistics available for this particular league.)

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
And how do we know what team a player is in?

6. Registered User
Join Date
Feb 2015
Posts
4
Originally Posted by gvee
And how do we know what team a player is in?
The results table has a team identifier (e.g., "TeamA"). The schedule table has the same identifier but is differentiated by home and away.

Results:
Code:
```player     team       points
-----------------------------
DanSmith   TeamA      4```
Schedule:
Code:
```home       away       date
------------------------------------------
TeamA      TeamB      2015-01-01 19:05:00```

7. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Can you provide the CREATE statements for all relevant tables, please?
Your above isn't clear, because the results table doesn't tell me when/which game the points were scored.

8. Registered User
Join Date
Feb 2015
Posts
4
Originally Posted by gvee
Can you provide the CREATE statements for all relevant tables, please?
Your above isn't clear, because the results table doesn't tell me when/which game the points were scored.
Thank you for your continued help!

I was trying to keep it simple and exclude irrelevant stuff. Here's the complete picture.

Code:
```CREATE TABLE `schedule_master` (
`date_yyyymmdd` varchar(8) NOT NULL DEFAULT '',
`date_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`visitor` varchar(8) NOT NULL DEFAULT '',
`home` varchar(8) NOT NULL DEFAULT '',
`type` varchar(10) DEFAULT NULL,
`notes` varchar(100) DEFAULT NULL,
PRIMARY KEY (`date_yyyymmdd`,`visitor`,`home`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1```
Here you have visitor, home, and date.

Code:
```CREATE TABLE `results_skaters` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`date_yyyymmdd` varchar(8) NOT NULL DEFAULT '',
`date_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`visitor` varchar(8) NOT NULL DEFAULT '',
`home` varchar(8) NOT NULL DEFAULT '',
`player` varchar(50) DEFAULT NULL,
`goals` tinyint(4) DEFAULT '0',
`assists` tinyint(4) DEFAULT '0',
`bonus` varchar(100) DEFAULT NULL,
`info` varchar(50) DEFAULT NULL,
`total_points` smallint(6) DEFAULT '0',
`season` varchar(8) NOT NULL DEFAULT '',
`test` varchar(8) NOT NULL DEFAULT '',
PRIMARY KEY (`uid`),
UNIQUE KEY `uid` (`uid`),
KEY `date_yyyymmdd` (`date_yyyymmdd`,`date_stamp`,`visitor`,`home`,`player`,`goals`,`assists`,`bonus`,`info`,`total_points`,`season`,`test`),
KEY `date_stamp` (`date_stamp`,`player`,`total_points`,`season`,`test`),
KEY `date_stamp_2` (`date_stamp`,`player`,`goals`,`assists`,`bonus`,`info`,`total_points`,`season`,`test`),
KEY `uid_2` (`uid`,`date_yyyymmdd`,`date_stamp`,`visitor`,`home`,`player`,`goals`,`assists`,`bonus`,`info`,`total_points`,`season`,`test`)
) ENGINE=MyISAM AUTO_INCREMENT=62731 DEFAULT CHARSET=latin1```
I'd use date_stamp for the date. The player is player. I don't actually have the player's team, I have the home/away pair for the game in which the scoring happened.
Last edited by Zudnic; 02-18-15 at 12:33.

9. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328