SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to query the database for only one record per year in stead of entire record set?

    Is there a way that I can do an MySQL query and find only one record per year?

    I need to confirm that there is at least one game for a baseball team for a particular year.

    Right now I am having to find all the games for all the years, then use array_unique to strip out the duplicate years.

    For example, the teams below would have the following records.
    Team A: 40 games + 42 games + 38 games equals 120 games
    Team B: 42 games + 43 games equals 85 games
    Team C: 38 games + 40 games equals 78 games

    (Note: I had to use Html <pre> tag to get my columns to line up)
    HTML Code:
    <pre>
                                     Games Per Season
                             2012        2013        2014
    
    Team A                   40           42           38
    Team B                    0           42           43
    Team C                   38           40            0
    
    </pre>
    I can do a query for Team A and find all the years for which Team A had games. That returns 120 games. Then use array_unique() to strip out the duplicate years. The years array for Team A will then have three elements. The years array would look like: array(0=>2012, 1=>2013,2=>2014)

    I can do a query for Team B and find all the years for which Team B had games. That returns 85 games. Then use array_unique() to strip out the duplicate years. The years array for Team A will then have just two elements (since there are games for only two years). The years array for Team B would look like: array(0=>2013, 1=>2014)

    I can do a query for Team C and find all the years for which Team C had games. That returns 78 games. Then use array_unique() to strip out the duplicate years. The years array for Team C will then also have just two elements (since there are games for only two years). The years array for Team C would look like: array(0=>2012, 1=>2013)
    .

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,266
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    OK, assuming you've got a table structure like this:
    Table format:
    Game
    AwayTeamID
    HomeTeamID
    GameDate
    Team
    TeamID
    TeamName
    You'll need to get a count of home games per year, union them with a count of away games per year, and then get a total count from there. Something like this (not checked for syntax since I don't have mySQL available - this is converted from SQL Server

    SELECT TeamName
    , GameYear
    , SUM(GameCount)
    FROM (SELECT TeamName
    , DATE_FORMAT(GameDate, '%Y') AS GameYear
    , COUNT(*) GameCount
    FROM Team T
    JOIN Game G ON T.TeamID = G.AwayTeamID
    GROUP BY TeamName
    , DATE_FORMAT(GameDate, '%Y')
    UNION ALL
    SELECT TeamName
    , DATE_FORMAT(GameDate, '%Y') AS GameYear
    , COUNT(*) GameCount
    FROM Team T
    JOIN Game G ON T.TeamID = G.HomeTeamID
    GROUP BY TeamName
    , DATE_FORMAT(GameDate, '%Y')) A
    GROUP BY TeamName
    , GameYear
    ORDER BY TeamName
    , GameYear
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Volitics View Post
    I need to confirm that there is at least one game for a baseball team for a particular year.
    so you want to run a query with two literals hardcoded in, the team id and the year?

    SELECT COUNT(*) FROM games WHERE teamid = 'A' AND year = 2013

    this just seems way too easy

    perhaps you would like to re-state the requirement?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    so you want to run a query with two literals hardcoded in, the team id and the year?

    SELECT COUNT(*) FROM games WHERE teamid = 'A' AND year = 2013

    this just seems way too easy

    perhaps you would like to re-state the requirement?
    Year is what I am trying to find. All I want to do is see Team A, Team B, and Team C have at least one game each year.

    The select statement below is returning:
    120 records for Team A (needs to be 2012, 2013, 2014 - only three records)
    85 records for Team B (needs to be 2013, 2014 - only two records)
    78 records for Team C (needs to be 2012, 2013 - only two records)

    I then have to use array_unique to remove the duplicate years.

    All I need to do is verify if there is/is not at least one game per year for each team.

    SELECT Year FROM Games
    .

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Volitics View Post
    All I want to do is see Team A, Team B, and Team C have at least one game each year.
    this is different from what you previously posted
    Quote Originally Posted by Volitics View Post
    All I need to do is verify if there is/is not at least one game per year for each team.
    for all teams, in other words?

    your specifications need polish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    It's basically a true/false scenario.

    A different way of looking at the problem is that I need one db query to tell me the below:

    Does Team A have games for 2012? True or False: True
    Does Team A have games for 2013? True or False: True
    Does Team A have games for 2014? True or False: True

    Does Team B have games for 2012? True or False: False
    Does Team B have games for 2013? True or False: True
    Does Team B have games for 2014? True or False: True

    Does Team C have games for 2012? True or False: True
    Does Team C have games for 2013? True or False: True
    Does Team C have games for 2014? True or False: False

    I have had the same problem before (working on a baseball score website) and have yet to figure it out.

    I have an "SQL for Dummies" book, it's a pretty good book, and it does not seem to have the answer.

    The first response by DaveMaxwell is so complicated I might study on it for a week and still not figure it out.

    MySQL may not have the functionality to do this kind of select query without complex coding.
    .

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,266
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Volitics View Post
    The first response by DaveMaxwell is so complicated I might study on it for a week and still not figure it out.
    It's not complicated - I gave you a best guess based on the limited information you provided (and a misunderstanding of your problem).

    I assumed two tables, one for team information, and one for the games played, with the teams defined as home and away in those games. My query counted each home game for each team per year, then each away game for each team per year, and gave you a total played for each team broken down by year.

    So if:

    • Team A played 10 home/10 away in 2011, 20 home/10 away in 2012 and 30 home/15 away in 2014
    • Team B played no games in 2011, 10 home/10 away in 2012 and 40 home/20 away in 2014
    • Team C played 20 home/ 20 away in 2011, 10 home/5 away in 2012 and no games in 2014


    You would get the following result
    1. Team A, 2012, 20
    2. Team A, 2013, 30
    3. Team A, 2014, 45
    4. Team B, 2012, 20
    5. Team B, 2013, 60
    6. Team C, 2011, 40
    7. Team C, 2012, 15


    Notice that Team B has no 2011 record, nor does Team C have no 2013 record. So your PHP would have to handle the empties accordingly.

    Rudy's suggestion of just querying for the criteria you're looking for in simpler queries may be the right answer. Or if you want to just see the years which the teams played in, you could do something like:

    [sql]
    SELECT TeamID
    , DATE_FORMAT(GameDate, '%Y') AS GameYear
    FROM Games
    GROUP BY TeamID
    , DATE_FORMAT(GameDate, '%Y') AS GameYear
    HAVING COUNT(*) > 1
    [/sql]
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Volitics View Post
    A different way of looking at the problem is that I need one db query to tell me the below:
    once again your specs are wavering all over the place

    from this latest post, it looks like you want exactly 9 rows of information produced -- for only those three teams, for only those three years

    is that correct?

    also, it would be a good idea to show us your table -- please do a SHOW CREATE TABLE for it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, there are 302 teams. I was just trying to simplify the problem.

    The number of D1 teams fluctuate each year. Year before last there were 297 teams. They vary. There are about 9,000 D1 games per year. Each team has about 50 to 60 games.

    I need to find out which teams played D1 games for 2012, for 2013, and for 2014.

    Did team 1 play games in all three years? Did team 2 play games in all three years. Did team 3 etc. etc etc.

    PHP Code:
    CREATE TABLE `Games` (
      `
    team_idsmallint(5unsigned default NULL,
      `
    Yearyear(4NOT NULL,
      `
    Monthtinyint(2unsigned default NULL,
      `
    Daytinyint(2unsigned default NULL,
      `
    game_idmediumint(8unsigned default NULL,
      `
    game_id_2mediumint(8unsigned default NULL COMMENT 'Is the same for both game records for this game; null if non-d1 team',
      `
    GameDateint(10unsigned default NULL,
      `
    GameOrdertinyint(2NOT NULL default '1',
      `
    Stadiumset('vs','at','neu','t','c','r','sr','cws'NOT NULL default 'vs',
      `
    team_id_oppsmallint(5unsigned default NULL,
      `
    NonD1varchar(75NOT NULL COMMENT 'Not D1 opponent',
      `
    ConferenceGameset('','*'NOT NULL,
      `
    DivisionalPlayset('','*'NOT NULL,
      `
    Commentvarchar(75NOT NULL COMMENT 'For footnotes',
      `
    Statusset('Scheduled','Done','Cancelled','Hide'NOT NULL,
      `
    ShowGameset('Yes','No'NOT NULL default 'Yes',
      `
    Inningstinyint(2unsigned default NULL,
      `
    Outcomeset('','W','L','T'NOT NULL,
      `
    MemberEditint(10unsigned default NULL COMMENT 'Time stamp for when member edits.',
      `
    MemberIPvarchar(45NOT NULL,
      `
    RunsScoredtinyint(3unsigned default NULL,
      `
    OpponentScoretinyint(3unsigned default NULL,
      `
    tourn_idsmallint(5unsigned default NULL,
      `
    TournRoadTeamset('','Yes','No'NOT NULL,
      `
    TournHomeTeamset('','Yes','No'NOT NULL,
      `
    TournGameNumsmallint(3unsigned default NULL,
      `
    Brackettinyint(1) default NULL COMMENT 'CWS has brackets.',
      `
    Eliminatedset('','Yes'NOT NULL,
      `
    ChampionshipGameset('','Yes'NOT NULL,
      `
    Finalsset('','Yes'NOT NULL COMMENT 'CWS has finals',
      
    UNIQUE KEY `game_id` (`game_id`),
      
    KEY `team_id` (`team_id`),
      
    KEY `Year` (`Year`),
      
    KEY `GameDate` (`GameDate`),
      
    KEY `GameOrder` (`GameOrder`),
      
    KEY `ConferenceGame` (`ConferenceGame`),
      
    KEY `DivisionalPlay` (`DivisionalPlay`),
      
    KEY `tourn_id` (`tourn_id`),
      
    KEY `game_id2` (`game_id_2`),
      
    KEY `Stadium` (`Stadium`),
      
    KEY `Status` (`Status`)
    ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.'
    .

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    how to you decide if it's a D1 game?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •