0

I have a database setup with some sample data. I have a number of teams in one table and their fixtures in another in their fixtures I have their scores for the games they have played already. I am struggling with the logic of calculating their wins draws and losses. should I update this in the teams table when updating the results (in fixtures table) or calculate it from scores in the fixtures table. I was reluctant to do it the first way as it may be concidered duplicate data but can't figure out the logic of how to calculate it. as you can probably tell this is the first database I have worked on with relationships between tables.

relavent section on er diagram

I am trying to present data from the above tables in to a league table. in order to get points I need to calculate games won/d/lost and that is what I can't figure out how to do (count the number for times home team out socres away team etc)

I will remove most cols from teams if I can calculate it from fixtures table.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Lonergan6275
  • 1,938
  • 6
  • 32
  • 63
  • 1
    I'm unsure of what exactly your real question here is but I'd read up on SQL a bit. You're going to want to perform calculations in SQL rather than in PHP. Hope this helps. – brs14ku Mar 24 '15 at 20:46
  • 1
    Put your test DB/data in a [SQLfiddle](http://sqlfiddle.com/) – Sammitch Mar 24 '15 at 20:51
  • thanks @brs14ku that helps some what i eddited the question to hopefully add some clarity. – Lonergan6275 Mar 24 '15 at 20:53
  • @Sammitch ill check that out and get back (never used it before). – Lonergan6275 Mar 24 '15 at 20:53
  • @Sammitch i am getting this err in sqlfiddle Access denied for user 'user_9_3b59f'@'%' to database 'db_Name' – Lonergan6275 Mar 24 '15 at 21:07
  • @Sammitch Regarding your edit, thank you for fixing 1 word in the title, but actually all the other words were misspelled too, ah ah. – Cœur May 07 '19 at 16:14

1 Answers1

1

Datasets and calculations relating to various flavors of sportsball are surprisingly complex. I've written code to generate fixture schedules based on arena availability, and it's not fun.

Anyhow, in order to generate the report you're after without duplicating data all over the place something like the below should work, though I haven't been able to test it.

SELECT t.team_name,
    hr.home_win, hr.home_loss, hr.home_draw,
    ar.away_win, ar.away_loss, ar.away_draw
FROM teams t
    -- home record
    INNER JOIN (
        SELECT home_team AS 'team_name',
            SUM(IF(home_team_score>away_team_score,1,0)) AS 'home_win',
            SUM(IF(home_team_score<away_team_score,1,0)) AS 'home_loss',
            SUM(IF(home_team_score=away_team_score,1,0)) AS 'home_draw'
        FROM fixtures
        GROUP BY home_team
    ) hr ON t.team_name = hr.team_name
    -- away record
    INNER JOIN (
        SELECT away_team AS 'team_name',
            SUM(IF(home_team_score<away_team_score,1,0)) AS 'away_win',
            SUM(IF(home_team_score>away_team_score,1,0)) AS 'away_loss',
            SUM(IF(home_team_score=away_team_score,1,0)) AS 'away_draw'
        FROM fixtures
        GROUP BY away_team
    ) ar ON t.team_name = ar.team_name

Now, a normal RDBMS would just use COUNT(scoreA>scoreB), but since this is MySQL I had to fudge it with SUM(IF()). fiddle

Assuming that you're not going to have thousands of teams this should scale reasonably well.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • Thanks that works great although it took commenting the count line in the fiddle and un-commenting the `SUM(IF())` line to get the correct results. – Lonergan6275 Mar 26 '15 at 15:30