Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2015
    Posts
    4

    Question 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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What if the player has only played 4/5 of the teams games; do you still want to divide by 5?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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
    Dave

  4. #4
    Join Date
    Feb 2015
    Posts
    4
    Quote Originally Posted by gvee View Post
    What if the player has only played 4/5 of the teams games; do you still want to divide by 5?
    Quote Originally Posted by dav1mo View Post
    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. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And how do we know what team a player is in?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2015
    Posts
    4
    Quote Originally Posted by gvee View Post
    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. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2015
    Posts
    4
    Quote Originally Posted by gvee View Post
    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. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    ok, so you take what I gave you previously and you left outer join it to your schedule table.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •