Trouble Calculating a "score" when the Value is Negative

S

StevenSLO

To anyone that can help:

I am trying to write a "team record" query from a "team" and
"team_match" tables that produce a record (sum of wins, losses, and
score) for games that have been played. One stipulation that we must
take into account in the "team record" query are forfeits which result
in a loss of 10 in the score column.

In the current query below, the negative scores are not subtracted
from the current score (a score of "0" is added) instead of
subtracting 10 ("-10"). Does anyone see a better way (the correct
way) to write a query that will incorporate the -10 score that is
stored in the "team_match" table? Thank You.


**Full-Schedule query**
SELECT m.matchID, m.date, m.time, m.tablenum,
t.team_name, tm.winner, tm.score
FROM match m INNER JOIN team_match tm
ON (m.matchid = tm.matchid)
INNER JOIN team t ON (tm.teamID = t.teamID)

CREATE VIEW Losers AS
SELECT Team_Match.TeamID, Team.Team_Name, COUNT(Team_Match.Winner)
Losses
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 0
GROUP BY Team_Match.TeamID, Team.Team_Name

CREATE VIEW Winners AS
SELECT Team_Match.TeamID, Team.Team_Name, COUNT(Team_Match.Winner)
Wins, SUM(Team_Match.Score) "Total Score"
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 1
GROUP BY Team.Team_Name, Team_Match.TeamID

CREATE VIEW "Team Record" AS
SELECT t.Team_Name, ISNULL(w.Wins, 0) Wins, ISNULL(l.Losses, 0)
Losses,
ISNULL(w.[Total Score], 0) "Total Score"
FROM (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Losses
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 0
GROUP BY Team_Match.TeamID, Team.Team_Name) l
FULL OUTER JOIN Team t ON (l.TeamID = t.TeamID)
FULL OUTER JOIN (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Wins, SUM(Team_Match.Score) "Total Score"
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 1 GROUP BY Team.Team_Name,
Team_Match.TeamID) w ON (t.TeamID = w.TeamID)
 
K

Ken Sheridan

What goes in the winner column for a forfeit, a zero? And if so does this
count as a loss in the sum of losses? I'd have assumed so, and if this is
the case, as 1 indicates a win and 0 a loss, can't you just sum the value of
the winner column to get the number of wins, and sum the absolute value of
the winner column -1 to get the number of losses:

SUM(winner) AS Wins

SUM (ABS(winner – 1)) AS Losses
or:
SUM((winner – 1)*-1) AS Losses

For the total score SUM(score) should cater for both positive and negative
values, giving you the sum of actual scores less 10 for each forfeited match..

If the above does fit the bill it would then be simply a case of joining the
three tables, grouping by Team_ID and Team_Name and returning the wins,
losses and total score per team as above.

Ken Sheridan
Stafford, England

StevenSLO said:
To anyone that can help:

I am trying to write a "team record" query from a "team" and
"team_match" tables that produce a record (sum of wins, losses, and
score) for games that have been played. One stipulation that we must
take into account in the "team record" query are forfeits which result
in a loss of 10 in the score column.

In the current query below, the negative scores are not subtracted
from the current score (a score of "0" is added) instead of
subtracting 10 ("-10"). Does anyone see a better way (the correct
way) to write a query that will incorporate the -10 score that is
stored in the "team_match" table? Thank You.


**Full-Schedule query**
SELECT m.matchID, m.date, m.time, m.tablenum,
t.team_name, tm.winner, tm.score
FROM match m INNER JOIN team_match tm
ON (m.matchid = tm.matchid)
INNER JOIN team t ON (tm.teamID = t.teamID)

CREATE VIEW Losers AS
SELECT Team_Match.TeamID, Team.Team_Name, COUNT(Team_Match.Winner)
Losses
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 0
GROUP BY Team_Match.TeamID, Team.Team_Name

CREATE VIEW Winners AS
SELECT Team_Match.TeamID, Team.Team_Name, COUNT(Team_Match.Winner)
Wins, SUM(Team_Match.Score) "Total Score"
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 1
GROUP BY Team.Team_Name, Team_Match.TeamID

CREATE VIEW "Team Record" AS
SELECT t.Team_Name, ISNULL(w.Wins, 0) Wins, ISNULL(l.Losses, 0)
Losses,
ISNULL(w.[Total Score], 0) "Total Score"
FROM (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Losses
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 0
GROUP BY Team_Match.TeamID, Team.Team_Name) l
FULL OUTER JOIN Team t ON (l.TeamID = t.TeamID)
FULL OUTER JOIN (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Wins, SUM(Team_Match.Score) "Total Score"
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 1 GROUP BY Team.Team_Name,
Team_Match.TeamID) w ON (t.TeamID = w.TeamID)
Click to expand...
 
S

StevenSLO

Ken-

If a team forfeits, then yes it counts as a loss for them. A
"true" (or a value of 1) is stored in the loser column, a "false" (or
a value of 0) is stored in the winner column, and a "-10" value is
stored in the score column. We are not having a problem calculating
the wins and losses; as shown in the CREATE Winners and Losers views,
we use the COUNT() function to calculate a teams wins and losses. Our
problem is in the calculation of the score column. If a team has a
"-10" value stored it simply does not account for the "-10"; it
calculates it as a 0 or something, I do not know. (As a side note, if
a team has all positive numbers then there's no problem calculating
the correct score.)

As seen in our CREATE Team Record view, we join the the 3 tables and
use the COUNT() function to calculate the totals wins and losses and
the SUM() function to calculate the score for a team; just like what
you said. Can you look at our SQL statement below and see if it is
what you were thinking.


CREATE VIEW "Team Record" AS
SELECT t.Team_Name, ISNULL(w.Wins, 0) Wins, ISNULL(l.Losses, 0)
Losses,
ISNULL(w.[Total Score], 0) "Total Score"
FROM (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Losses
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 0
GROUP BY Team_Match.TeamID, Team.Team_Name) l
FULL OUTER JOIN Team t ON (l.TeamID = t.TeamID)
FULL OUTER JOIN (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Wins, SUM(Team_Match.Score) "Total Score"
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 1 GROUP BY Team.Team_Name,
Team_Match.TeamID) w ON (t.TeamID = w.TeamID)


Thanks!
Steven
 
K

Ken Sheridan

Steven:

I can't put my finger on why the negative values are not being included in
your summation, but the point I was making is that it looks to me like it
should be possible to simplify things considerably, avoiding the two
subqueries for counting the wins and losses by summing rather than counting,
the sum of a set of ones being the same as a count of them, and doing a
straightforward summation of the scores column, which should take account of
the negative values as a matter of course:

SELECT
team_name,
SUM(winner) AS Wins,
SUM(ABS(winner – 1)) AS Losses,
SUM(scores) AS TotalScore
FROM match INNER JOIN
team_match INNER JOIN team
ON team_match.teamID = team.teamID
ON team_match.matchID = match.matchID
GROUP BY team_name;

Ken Sheridan
Stafford, England
 
S

StevenSLO

Ken-

I took some of your suggestions and came up with the correct query!
Here is what I ended up with:

SELECT t.team_name 'Team Name',Sum(case when tm.winner = 1 then 1 else
0 end) Wins, Sum(case when tm.winner = 0 then 1 else 0 end) Losses,
Sum(Score) Score
FROM team_match tm
INNER JOIN Team t
ON t.teamid = tm.teamid
GROUP BY t.team_name
ORDER BY Sum(case when tm.winner = 1 then 1 else 0 end) DESC,
Sum(Score) DESC;

THANK YOU VERY MUCH for your help! Have a good day.

Steven



Steven:

I can't put my finger on why the negative values are not being included in
your summation, but the point I was making is that it looks to me like it
should be possible to simplify things considerably, avoiding the two
subqueries for counting the wins and losses by summing rather than counting,
the sum of a set of ones being the same as a count of them, and doing a
straightforward summation of the scores column, which should take account of
the negative values as a matter of course:

SELECT
team_name,
SUM(winner) AS Wins,
SUM(ABS(winner - 1)) AS Losses,
SUM(scores) AS TotalScore
FROM match INNER JOIN
team_match INNER JOIN team
ON team_match.teamID = team.teamID
ON team_match.matchID = match.matchID
GROUP BY team_name;

Ken Sheridan
Stafford, England

StevenSLO said:
Ken-
Click to expand...
If a team forfeits, then yes it counts as a loss for them. A
"true" (or a value of 1) is stored in the loser column, a "false" (or
a value of 0) is stored in the winner column, and a "-10" value is
stored in the score column. We are not having a problem calculating
the wins and losses; as shown in the CREATE Winners and Losers views,
we use the COUNT() function to calculate a teams wins and losses. Our
problem is in the calculation of the score column. If a team has a
"-10" value stored it simply does not account for the "-10"; it
calculates it as a 0 or something, I do not know. (As a side note, if
a team has all positive numbers then there's no problem calculating
the correct score.)
Click to expand...
As seen in our CREATE Team Record view, we join the the 3 tables and
use the COUNT() function to calculate the totals wins and losses and
the SUM() function to calculate the score for a team; just like what
you said. Can you look at our SQL statement below and see if it is
what you were thinking.
Click to expand...
CREATE VIEW "Team Record" AS
SELECT t.Team_Name, ISNULL(w.Wins, 0) Wins, ISNULL(l.Losses, 0)
Losses,
ISNULL(w.[Total Score], 0) "Total Score"
FROM (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Losses
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 0
GROUP BY Team_Match.TeamID, Team.Team_Name) l
FULL OUTER JOIN Team t ON (l.TeamID = t.TeamID)
FULL OUTER JOIN (SELECT Team_Match.TeamID, Team.Team_Name,
COUNT(Team_Match.Winner) Wins, SUM(Team_Match.Score) "Total Score"
FROM Team JOIN Team_Match ON (Team.TeamID = Team_Match.TeamID)
WHERE Team_Match.Winner = 1 GROUP BY Team.Team_Name,
Team_Match.TeamID) w ON (t.TeamID = w.TeamID)
Click to expand...
Thanks!
Steven
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ranking 1
cannot get null value to equal 0 6
Access 07 Lockup 0
Trouble with Query doubling values 1
Min not working 2
Get the reverse of a query 13
Query to find the Percentage of items in a field 3
League standings based off final scores 5

Top