Need assistance with SUM and MAX

[Tony]

I've already tried searching posts, but haven't found any similiar solutions
to this. I'm new to SQL and am using Access 2007 as my development
environment. I'm trying to keep my solution as simple as possible.

I have two relationships between two tables as follows:
GamesTable:
ID - Autonumber
Team1ID - Number (Relationship w/ TeamsTable ID)
Team2ID - Number (Relationship w/ TeamsTable ID)
Team1Score - Number
Team2Score - Number
Team1Points - Number
Team2Points - Number

TeamsTable:
ID - Autonumber
TeamName - Text

The question I'm trying to answer is to find the team that won the league.
I've been successful with finding the sum of scores for each team. The
problem lies in when I try to find out who has the most points. My code is
as follows:

SELECT Sum(Games.Team1Points) AS SumOfTeam1Points
FROM Games
GROUP BY Games.Team1ID;

Attempting to use MAX in my select statement around the SUM function is
invalid, though I'm not sure why.

Since I need to find the name of the team that won, I'm of the belief that I
need to return the correct TeamID for the team that won back to my
TeamsTable so I can then get the name. I'm under the impression I need a
subquery here, but I'm lost as to how I can return the ID of the team that
has the most points once I've summed their respective points.

Any help appreciated.

Thank you,

Tony

 

[Ken Sheridan]

Tony:

Einstein once said, when asked about explaining the theory of general
relativity, that you can make things as simple as possible, but no more so.
So here goes.

Firstly your GamesTable is not well structured. Having separate columns for
each team, score and points is what’s known in the jargon as ‘encoding data
as column headings’, whereas in a relational database data should only be
stored as values at column positions in rows in tables; this is known as the
‘information principle’. So the table should be decomposed into a Games
table with columns GameID, GameDate, Venue etc, and a GameTeams table with
columns GameID (a foreign key in this case) TeamID, Score, Points and any
others you need such as one to indicate if its the Home or Away team. The
primary key of this table would be a composite one made up of the GameID and
TeamID columns. The table in fact models the many-to-many relationship type
between Teams and Games.

With the points in one column in a table you can then easily sum the points
per team with:

SELECT Team, SUM(Points) AS TotalPoints
FROM GameTeams INNER JOIN Teams
ON GameTeams.TeamID = Teams.TeamID
GROUP BY Team;

To show only the team(s) with the highest number of points you can sort the
results in descending order and return the TOP 1 row(s):

SELECT TOP 1 Team, SUM(Points) AS TotalPoints
FROM GameTeams INNER JOIN Teams
ON GameTeams. TeamID = Teams.TeamID
GROUP BY Team
ORDER BY SUM(Points) DESC;

You can of course include any other columns from the Teams table in the
query so long as you include them in its GROUP BY Clause as well as in the
SELECT clause.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Tony:

An unwanted space crept into my reply. The query should be:

SELECT TOP 1 Team, SUM(Points) AS TotalPoints
FROM GameTeams INNER JOIN Teams
ON GameTeams.TeamID = Teams.TeamID
GROUP BY Team
ORDER BY SUM(Points) DESC;

You can of course include any other columns from the Teams table in the
query so long as you include them in its GROUP BY Clause as well as in the
SELECT clause.

Ken Sheridan
Stafford, England

 

[Tony]

Thank you Ken. I'll have to digest this more once I've had some breakfast.
Though I'm not sure I want to alter the design of the tables right now, I
agree with you that I 'should' based on your reasoning. Thank you for
explaining the why as well, no point in learning how to do something without
knowing why.

Tony

 

[John Spencer]

SELECT TeamName, Sum(Games.Team1Points) as Score
FROM GAMES INNER JOIN TeamTable
ON Games.Team1Id = TeamsTable.TeamID
GROUP BY TeamName

HAVING Sum(Team1Points) =

(SELECT Max(A.SumPoints)
FROM
(SELECT Sum(Games.Team1Points)SumPoints
FROM Games
GROUP BY Games.Team1ID) as A)


A real problem here is that your table structure for GamesTable makes it
difficult to get all the points for any one team - since a team could be
referenced as either team1 or team2 and the relevant points could be
stored in team1Score or Team2Score.

You could use a UNION query to normalize the date
SELECT Team1Id, Team1Score, Team1Points
FROM GamesTable
UNION ALL
SELECT Team2ID, Team2Score, Team2Points
FROM GamesTable

And substitute the Union query for the games table in the above query.
A better structure might look like

Teams (Table)
TeamID
TeamName

Games (Table)
GameNumber
TeamID
GameDate
....

GameScores (Table)
GameNumber
TeamID
Score
Points

Then the query you want would be

SELECT TOP 1 Teams.TeamName
, Sum(GameScores.Points)
FROM Teams INNER JOIN GameScores
ON Teams.TeamID = GameScores.TeamID
Group By Teams.TeamName
ORDER BY Sum(GameScores.Points) Desc


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Tony]

Hi John,

Thanks for your help here as well. You nailed it on my problem with having a
team referenced as either team1 or team2 and their points could be stored in
either team1Points or team2Points. So per your suggestions, and also Ken's,
I'm in the process of restructuring my DB to work in manner more inline with
proper relational DB design.

When attempting to run this, I am prompted with a dialog asking for the
Teams.TeamID. If I enter 1, I get every TeamName and the score 7 for each
(this is the max sum from data I have entered). Why is every team listed? If
I alter it to the following, I still get the dialog prompt, but only for the
team I'm interested in.

SELECT TOP 1 TeamName, Sum(Games.Team1Points) as Score
FROM GAMES INNER JOIN Teams
ON Games.Team1Id = Teams.TeamID
GROUP BY TeamName

HAVING Sum(Team1Points) =

(SELECT Max(A.SumPoints)
FROM
(SELECT Sum(Games.Team1Points) AS SumPoints
FROM Games
GROUP BY Games.Team1ID) as A);

Thanks again for your help. It's nice to understand why SQL works the way it
does. I'm finally starting to see how this subquery stuff works. The alias
naming thing is a bit confusing reading the book (Modern Database Mgmt. By
Jeffrey A. Hoffer, Prescott, McFadden). The book states how it's just a way
to rename the column heading. Coming from a "intermediate" level 'C'
programming background, I see though that you have used this alias to
reference the collection. I've seen this before (namely for table names),
and the way I relate to it is establishing a variable. E.g. int someNumber;
Is this a properly analogy?

Tony

 

[Tony]

Hi John,

One more question here. I'm trying to understand your design and the
relationship between Teams, Games, GameScores.

Based on your design below, I am guessing the following:

Teams (Table) Games (Table) GameScores (Table)
TeamID TeamID TeamID
TeamName GameNumber GameNumber
GameDate Score
Points

Games & GameScores have a relationship between TeamID in Teams Table. Each
Team has many games and game scores.
Games and GameScores have a relationship between GameNumber. Each game
having many game scores.

Is this correct?

Thanks,

Tony

 

[Tony]

Hi Ken,

Your explanations in conjunction with John's are helping me make sense of
this. I've been struggling with how to model a many-to-many relationship. It
seems that I need to break it down so that each table only contains data
relating to the entity in question. The whole normalizing concept correct?

I'll change my design based on the feedback you and John gave. Thanks for
the help.

Tony

 

[John Spencer]

I believe your understanding is correct.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

If you get a prompt for the fieldname, then the field name is probably a
typo. Check the spellling of the field name. Perhaps I screwed up and
referred to a field that does not exist in the table I referred to or
the table name is misstyped.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Tony]

Thank you John for your help. I'm actually accomplishing something now!

Tony