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

Databases
1

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)


<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

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

3

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?

4

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

this is different from what you previously posted

for all teams, in other words?

your specifications need polish :slight_smile:

6

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

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]

8

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

9

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.


CREATE TABLE `Games` (
  `team_id` smallint(5) unsigned default NULL,
  `Year` year(4) NOT NULL,
  `Month` tinyint(2) unsigned default NULL,
  `Day` tinyint(2) unsigned default NULL,
  `game_id` mediumint(8) unsigned default NULL,
  `game_id_2` mediumint(8) unsigned default NULL COMMENT 'Is the same for both game records for this game; null if non-d1 team',
  `GameDate` int(10) unsigned default NULL,
  `GameOrder` tinyint(2) NOT NULL default '1',
  `Stadium` set('vs','at','neu','t','c','r','sr','cws') NOT NULL default 'vs',
  `team_id_opp` smallint(5) unsigned default NULL,
  `NonD1` varchar(75) NOT NULL COMMENT 'Not D1 opponent',
  `ConferenceGame` set('','*') NOT NULL,
  `DivisionalPlay` set('','*') NOT NULL,
  `Comment` varchar(75) NOT NULL COMMENT 'For footnotes',
  `Status` set('Scheduled','Done','Cancelled','Hide') NOT NULL,
  `ShowGame` set('Yes','No') NOT NULL default 'Yes',
  `Innings` tinyint(2) unsigned default NULL,
  `Outcome` set('','W','L','T') NOT NULL,
  `MemberEdit` int(10) unsigned default NULL COMMENT 'Time stamp for when member edits.',
  `MemberIP` varchar(45) NOT NULL,
  `RunsScored` tinyint(3) unsigned default NULL,
  `OpponentScore` tinyint(3) unsigned default NULL,
  `tourn_id` smallint(5) unsigned default NULL,
  `TournRoadTeam` set('','Yes','No') NOT NULL,
  `TournHomeTeam` set('','Yes','No') NOT NULL,
  `TournGameNum` smallint(3) unsigned default NULL,
  `Bracket` tinyint(1) default NULL COMMENT 'CWS has brackets.',
  `Eliminated` set('','Yes') NOT NULL,
  `ChampionshipGame` set('','Yes') NOT NULL,
  `Finals` set('','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

how to you decide if it’s a D1 game?