Problems getting SUM from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problems getting SUM from table

Author

Topic

sgietz
Starting Member

5 Posts

Posted - 2011-01-21 : 11:38:03

Hello,

I'm trying to select some data. It sounds like a fairly simple operation, but it has me stumped for the moment. We have 3 tables that hold information about softball games. It's really simple. There's a teams table, a games table, and a scores table. Nothing fancy, no elaborate statistics.

I need to get the total runs for a team (that's easy) and also the runs against that team (my problem). Here are the tables with some sample data, so you get an idea of what I'm dealing with.


TEAMS
Id    Name
--------------------------------------
1     Team 1
2     Team 2

GAMES
Id    AwayTeam     HomeTeam
--------------------------------------
1     1            2
2     2            1

SCORES
Id    Runs     Team_ID     Game_ID
--------------------------------------
1     5        1           1
2     3        2           1
3     7        1           2
4     4        2           2

What I need to get is the total number of runs for and against each team. In the case of "Team 1" I want to get a table with the following data.


RESULT
Runs for     Runs against
---------------------------------------
12           7

I hope that makes sense. I'm just not sure what approach to take. I was thinking about loops, but I'm not sure if that's feasible. I appreciate your help :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-21 : 12:12:10

[code]DECLARE @SCORES TABLE (Id INT, Runs INT, Team_ID INT, Game_ID int)
INSERT @Scores(ID, Runs, Team_ID, Game_ID)
VALUES
(1, 5, 1, 1),
(2, 3, 2, 1),
(3, 7, 1, 2),
(4, 4, 2, 2)

-- Super Simple
SELECT
SUM(CASE WHEN S.Team_ID = 1 THEN S.Runs ELSE 0 END) AS RunsFor,
SUM(CASE WHEN S.Team_ID <> 1 THEN S.Runs ELSE 0 END) AS RunsAgainst
FROM
@Scores AS S

--If you know the Team_ID and want to pick that specific team
-- from amongst all the numerous teams you might have.
SELECT
SUM(CASE WHEN S.Team_ID = 1 THEN S.Runs ELSE 0 END) AS RunsFor,
SUM(CASE WHEN S.Team_ID <> 1 THEN S.Runs ELSE 0 END) AS RunsAgainst
FROM
@Scores AS S
INNER JOIN
(
SELECT Game_ID
FROM @Scores
WHERE Team_ID = 1
) AS T
ON S.Game_ID = T.Game_ID [/code]

sgietz
Starting Member

5 Posts

Posted - 2011-01-21 : 14:35:16

That works perfectly. Thanks! :)

sgietz
Starting Member

5 Posts

Posted - 2011-01-26 : 12:10:00

OK, now I have another issue. We added a new table "Leagues." Each team will belong to a league. Here are the changes.


TEAMS
Id    Name     League_ID
--------------------------------------
1     Team 1   1
2     Team 2   1

LEAGUES
Id    Name
--------------------------------------
1     League 1
2     League 2

How would I get a table of all teams with "Runs for" and "Runs against" for a specific league? I fiddles around with it using functions, but I just can't get it to work. Again, all I would know is the League ID. Then get all the teams in that league with their current runs.

Thanks! :)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-26 : 13:35:53

Why are Scores a separate entity and not an attribute of a game? That does not sound right; in fact, it sounds like an old George Carlin joke. "And now for some baseball scores -- 2, 7, 5 and 9". I think you have a version of attribute splitting here.

Please post real DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Try this

CREATE TABLE Team
(team_id INTEGER NOT NULL PRIMARY KEY,
team_name VARCHAR(12) NOT NULL,
etc);

CREATE TABLE Games
(game_nbr INTEGER NOT NULL PRIMARY KEY, -- season or year needed?
away_team_id INTEGER NOT NULL
REFERENCES Teams(team_id),
home_team_id INTEGER NOT NULL
REFERENCES Teams(team_id,
away_team_score INTEGER NOT NULL,
home_team_score INTEGER NOT NULL,
etc);

>> What I need to get is the total number of runs for and against each team. <<

SELECT T.team_name,
SUM(CASE WHEN T.team_id = G.away_team_id
THEN G.away_team_score
WHEN T.team_id = G.home_team_id
THEN G.home_team_score
ELSE 0 END) all_games_team_runs_tot

SUM(CASE WHEN T.team_id = G.away_team_id
THEN G.home_team_score
WHEN T.team_id = G.home_team_id
THEN G.away_team_score
ELSE 0 END) all_games_opponent_runs_tot
FROM Teams AS T, Games AS G
WHERE T.team_id IN (G.away_team_id, G.home_team_id)
GROUP BY T.team_name;

Good DEL means simple DML.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

sgietz
Starting Member

5 Posts

Posted - 2011-01-26 : 16:00:55

Thanks.

sgietz
Starting Member

5 Posts

Posted - 2011-01-27 : 12:34:30

All right. Now I need to include three more fields. The new fields are total wins, total losses, and the percentage of wins vs. losses. Below is the query I'm currently using after I modified the suggested one above.


SELECT T.TeamName,
SUM
(
	CASE WHEN T.ID = G.AwayTeam
	THEN G.AwayTeamScore
	WHEN T.ID = G.HomeTeam
	THEN G.HomeTeamScore
	ELSE 0 END
)
AS RunsFor,
SUM
(
	CASE WHEN T.ID = G.AwayTeam
	THEN G.HomeTeamScore
	WHEN T.ID = G.HomeTeam
	THEN G.AwayTeamScore
	ELSE 0 END
)
AS RunsAgainst
FROM Teams AS T
INNER JOIN Games AS G
ON (T.ID = G.HomeTeam OR T.ID = G.AwayTeam)
WHERE T.League_ID = @LeagueID
AND G.Season = (SELECT MAX(Season) FROM Softball.Games)
GROUP BY T.TeamName;

That gives me a table with each team (for a specific league) that includes the team name, the runs for, and runs against. How would I now get those other three fields in that same table? I tried for hours with functions to no avail. Help is appreciated. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources