Professional Web Applications Themes

summing data based on football seasons - MySQL

Hi, I have 3 tables season, fixture and scorers. season is defined as: seasonID int(11) name varchar(20) with data like: seasonID name 1 2005/2006 2 2006/2007 ------------------------------------------------------------------------------------------------- fixture are defined as: fixtureID int(11) seasonID int(11) typeID int(11) team varchar(30) date date time time homeScore int(11) awayScore int(11) with data like: fixtureID seasonID typeID team date time hS aS 2 2 1 Mount Vets 2006-08-17 19:25:00 0 0 3 2 2 The Railway 2006-08-25 21:45:00 0 0 4 1 1 Renford Rejects 2005-05-05 18:45:00 3 2 --------------------------------------------------------------------------------------------------- scorers are defined as: fixtureID int(11) playerID int(11) goals int(11) with data like: fixtureID ...

  1. #1

    Default summing data based on football seasons

    Hi,

    I have 3 tables season, fixture and scorers.

    season is defined as:

    seasonID int(11)
    name varchar(20)

    with data like:

    seasonID name
    1 2005/2006
    2 2006/2007

    -------------------------------------------------------------------------------------------------

    fixture are defined as:

    fixtureID int(11)
    seasonID int(11)
    typeID int(11)
    team varchar(30)
    date date
    time time
    homeScore int(11)
    awayScore int(11)

    with data like:

    fixtureID seasonID typeID team date time hS
    aS
    2 2 1 Mount Vets 2006-08-17 19:25:00 0 0
    3 2 2 The Railway 2006-08-25 21:45:00 0 0
    4 1 1 Renford Rejects 2005-05-05 18:45:00 3 2

    ---------------------------------------------------------------------------------------------------

    scorers are defined as:



    fixtureID int(11)
    playerID int(11)
    goals int(11)

    with data like:

    fixtureID playerID goals
    2 1 2
    2 2 1
    3 3 1
    4 1 2
    3 1 4


    -----------------------------------------------------------------------------------------------------


    The problem i am having is that i want to be able to get a summary of
    the goals scored by a player each season given there playerID much like
    below

    Goals for player 1
    -----------------------------

    Season Goals
    2005/2006 20
    2006/2007 100


    As i am using version 4.0.2 of mySQL (i have no choice as thats what my
    webspace provider uses) i can not use sub queries and this is proving
    to bit a bit of a problem for me.

    Any help on this will be much appreciated

    Thanks in advance

    Dom

    domdm@hotmail.com Guest

  2. #2

    Default Re: summing data based on football seasons

    [email]domdmhotmail.com[/email] wrote:
    > Hi,
    >
    > I have 3 tables season, fixture and scorers.
    >
    > season is defined as:
    >
    > seasonID int(11)
    > name varchar(20)
    >
    > with data like:
    >
    > seasonID name
    > 1 2005/2006
    > 2 2006/2007
    >
    > -------------------------------------------------------------------------------------------------
    >
    > fixture are defined as:
    >
    > fixtureID int(11)
    > seasonID int(11)
    > typeID int(11)
    > team varchar(30)
    > date date
    > time time
    > homeScore int(11)
    > awayScore int(11)
    >
    > with data like:
    >
    > fixtureID seasonID typeID team date time hS
    > aS
    > 2 2 1 Mount Vets 2006-08-17 19:25:00 0 0
    > 3 2 2 The Railway 2006-08-25 21:45:00 0 0
    > 4 1 1 Renford Rejects 2005-05-05 18:45:00 3 2
    >
    > ---------------------------------------------------------------------------------------------------
    >
    > scorers are defined as:
    >
    >
    >
    > fixtureID int(11)
    > playerID int(11)
    > goals int(11)
    >
    > with data like:
    >
    > fixtureID playerID goals
    > 2 1 2
    > 2 2 1
    > 3 3 1
    > 4 1 2
    > 3 1 4
    >
    >
    > -----------------------------------------------------------------------------------------------------
    >
    >
    > The problem i am having is that i want to be able to get a summary of
    > the goals scored by a player each season given there playerID much like
    > below
    >
    > Goals for player 1
    > -----------------------------
    >
    > Season Goals
    > 2005/2006 20
    > 2006/2007 100
    >
    >
    > As i am using version 4.0.2 of mySQL (i have no choice as thats what my
    > webspace provider uses) i can not use sub queries and this is proving
    > to bit a bit of a problem for me.
    >
    > Any help on this will be much appreciated
    >
    > Thanks in advance
    >
    > Dom
    >
    Unless I'm missing something, it's not possible. I don't see a link
    between the player and the season - unless possibly your team changes
    its fixture id for each season (which wouldn't make a lot of sense).

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: summing data based on football seasons


    Jerry Stuckle wrote:
    > [email]domdmhotmail.com[/email] wrote:
    > > Hi,
    > >
    > > I have 3 tables season, fixture and scorers.
    > >
    > > season is defined as:
    > >
    > > seasonID int(11)
    > > name varchar(20)
    > >
    > > with data like:
    > >
    > > seasonID name
    > > 1 2005/2006
    > > 2 2006/2007
    > >
    > > -------------------------------------------------------------------------------------------------
    > >
    > > fixture are defined as:
    > >
    > > fixtureID int(11)
    > > seasonID int(11)
    > > typeID int(11)
    > > team varchar(30)
    > > date date
    > > time time
    > > homeScore int(11)
    > > awayScore int(11)
    > >
    > > with data like:
    > >
    > > fixtureID seasonID typeID team date time hS
    > > aS
    > > 2 2 1 Mount Vets 2006-08-17 19:25:00 0 0
    > > 3 2 2 The Railway 2006-08-25 21:45:00 0 0
    > > 4 1 1 Renford Rejects 2005-05-05 18:45:00 3 2
    > >
    > > ---------------------------------------------------------------------------------------------------
    > >
    > > scorers are defined as:
    > >
    > >
    > >
    > > fixtureID int(11)
    > > playerID int(11)
    > > goals int(11)
    > >
    > > with data like:
    > >
    > > fixtureID playerID goals
    > > 2 1 2
    > > 2 2 1
    > > 3 3 1
    > > 4 1 2
    > > 3 1 4
    > >
    > >
    > > -----------------------------------------------------------------------------------------------------
    > >
    > >
    > > The problem i am having is that i want to be able to get a summary of
    > > the goals scored by a player each season given there playerID much like
    > > below
    > >
    > > Goals for player 1
    > > -----------------------------
    > >
    > > Season Goals
    > > 2005/2006 20
    > > 2006/2007 100
    > >
    > >
    > > As i am using version 4.0.2 of mySQL (i have no choice as thats what my
    > > webspace provider uses) i can not use sub queries and this is proving
    > > to bit a bit of a problem for me.
    > >
    > > Any help on this will be much appreciated
    > >
    > > Thanks in advance
    > >
    > > Dom
    > >
    >
    > Unless I'm missing something, it's not possible. I don't see a link
    > between the player and the season - unless possibly your team changes
    > its fixture id for each season (which wouldn't make a lot of sense).
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > [email]jstucklexattglobal.net[/email]
    > ==================
    I agree, this structure does look a little odd. Also mixing of naming
    styles - 'fixture','scorers' and a VARCHAR for something that will
    always be of a fixed length '2005/2006'. Really I guess there should be
    a field like season_fixture_id. Anyway, with the structure as it
    stands, a query like the following should work:

    SELECT season_id, sum( goals )
    FROM `fixture`
    LEFT JOIN scorers ON fixture.fixture_id = scorers.fixture_id
    WHERE player_id =1
    GROUP BY season_id
    LIMIT 0 , 30

    strawberry Guest

  4. #4

    Default Re: summing data based on football seasons

    Hi Thanks for your help,

    The naming convenstion was a typo (late last night when i posted the
    message). As for the connection between the tables. The fixture is the
    matches that are played which make up a season, the season table holds
    the names of the seasons/competitions that the team may be playing in
    and the scorers table is a linke between the fixture and the players
    that scored the goals with fixtureID and playerID as a compaound key.

    I dont see this as an odd structure, any other suggestions would be
    good as for this structure.

    Thanks

    Dom


    strawberry wrote:
    > Jerry Stuckle wrote:
    > > [email]domdmhotmail.com[/email] wrote:
    > > > Hi,
    > > >
    > > > I have 3 tables season, fixture and scorers.
    > > >
    > > > season is defined as:
    > > >
    > > > seasonID int(11)
    > > > name varchar(20)
    > > >
    > > > with data like:
    > > >
    > > > seasonID name
    > > > 1 2005/2006
    > > > 2 2006/2007
    > > >
    > > > -------------------------------------------------------------------------------------------------
    > > >
    > > > fixture are defined as:
    > > >
    > > > fixtureID int(11)
    > > > seasonID int(11)
    > > > typeID int(11)
    > > > team varchar(30)
    > > > date date
    > > > time time
    > > > homeScore int(11)
    > > > awayScore int(11)
    > > >
    > > > with data like:
    > > >
    > > > fixtureID seasonID typeID team date time hS
    > > > aS
    > > > 2 2 1 Mount Vets 2006-08-17 19:25:00 0 0
    > > > 3 2 2 The Railway 2006-08-25 21:45:00 0 0
    > > > 4 1 1 Renford Rejects 2005-05-05 18:45:00 3 2
    > > >
    > > > ---------------------------------------------------------------------------------------------------
    > > >
    > > > scorers are defined as:
    > > >
    > > >
    > > >
    > > > fixtureID int(11)
    > > > playerID int(11)
    > > > goals int(11)
    > > >
    > > > with data like:
    > > >
    > > > fixtureID playerID goals
    > > > 2 1 2
    > > > 2 2 1
    > > > 3 3 1
    > > > 4 1 2
    > > > 3 1 4
    > > >
    > > >
    > > > -----------------------------------------------------------------------------------------------------
    > > >
    > > >
    > > > The problem i am having is that i want to be able to get a summary of
    > > > the goals scored by a player each season given there playerID much like
    > > > below
    > > >
    > > > Goals for player 1
    > > > -----------------------------
    > > >
    > > > Season Goals
    > > > 2005/2006 20
    > > > 2006/2007 100
    > > >
    > > >
    > > > As i am using version 4.0.2 of mySQL (i have no choice as thats what my
    > > > webspace provider uses) i can not use sub queries and this is proving
    > > > to bit a bit of a problem for me.
    > > >
    > > > Any help on this will be much appreciated
    > > >
    > > > Thanks in advance
    > > >
    > > > Dom
    > > >
    > >
    > > Unless I'm missing something, it's not possible. I don't see a link
    > > between the player and the season - unless possibly your team changes
    > > its fixture id for each season (which wouldn't make a lot of sense).
    > >
    > > --
    > > ==================
    > > Remove the "x" from my email address
    > > Jerry Stuckle
    > > JDS Computer Training Corp.
    > > [email]jstucklexattglobal.net[/email]
    > > ==================
    >
    > I agree, this structure does look a little odd. Also mixing of naming
    > styles - 'fixture','scorers' and a VARCHAR for something that will
    > always be of a fixed length '2005/2006'. Really I guess there should be
    > a field like season_fixture_id. Anyway, with the structure as it
    > stands, a query like the following should work:
    >
    > SELECT season_id, sum( goals )
    > FROM `fixture`
    > LEFT JOIN scorers ON fixture.fixture_id = scorers.fixture_id
    > WHERE player_id =1
    > GROUP BY season_id
    > LIMIT 0 , 30
    domdm@hotmail.com Guest

  5. #5

    Default Re: summing data based on football seasons


    [email]domdmhotmail.com[/email] wrote:
    > Hi Thanks for your help,
    >
    > The naming convenstion was a typo (late last night when i posted the
    > message). As for the connection between the tables. The fixture is the
    > matches that are played which make up a season, the season table holds
    > the names of the seasons/competitions that the team may be playing in
    > and the scorers table is a linke between the fixture and the players
    > that scored the goals with fixtureID and playerID as a compaound key.
    >
    > I dont see this as an odd structure, any other suggestions would be
    > good as for this structure.
    >
    > Thanks
    >
    > Dom
    >
    >
    Yes, but Jerry's point was (and apologies if I'm quoting out of turn)
    that given a table like this (see below) and the fact that fixture_ids
    (probably) remain unchanged from season to season
    how does one know whether player_id 1 scored his goals in season 1 or
    season 2?

    fixtureID playerID goals
    2 1 2
    2 2 1
    3 3 1
    4 1 2
    3 1 4

    strawberry Guest

  6. #6

    Default Re: summing data based on football seasons

    [email]domdmhotmail.com[/email] wrote:
    > Hi Thanks for your help,
    >
    > The naming convenstion was a typo (late last night when i posted the
    > message). As for the connection between the tables. The fixture is the
    > matches that are played which make up a season, the season table holds
    > the names of the seasons/competitions that the team may be playing in
    > and the scorers table is a linke between the fixture and the players
    > that scored the goals with fixtureID and playerID as a compaound key.
    >
    > I dont see this as an odd structure, any other suggestions would be
    > good as for this structure.
    I guess the suggestions would depend on what the primary and/or unique keys are for your tables.

    In a DB I have that is similar, I have:

    season:
    season_id (U)
    season_description
    season_start
    season_end

    season_sched:
    schedule_entry_id (U)
    season_id (FK)
    game_id (FK)

    game:
    game_id (U)
    home_team_id (FK)
    away_team_id (FK)

    goal:
    game_id (U)
    player_id (FK)
    team_id (FK)
    goal_time

    Just about any form of statistics that are needed from this database are derived via views. The overall scores for games are also derived, so as to cut down the tion of data.

    --

    Murdoc Guest

Similar Threads

  1. Style changes based upon Data Values
    By e2biz in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: November 9th, 10:02 AM
  2. Populate XML data based on clicked link
    By Rick W. in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: April 29th, 12:32 PM
  3. Selecting data based on user input
    By Svein Olav Steinmo in forum ASP Database
    Replies: 3
    Last Post: September 23rd, 09:17 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139