CODEWITHSUNDEEP
phpmysqlrelational-databaserelationshiptable-relationships
Lonergan6275
Lonergan6275

Reputation: 2038

performing calculations on a database

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.

Upvotes: 0

Views: 171

Answers (1)

Sammitch
Sammitch

Reputation: 32272

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.

Upvotes: 1

Related Questions