August 15, 2014 at 12:06 pm
Can someone please help. I am trying to calculate games behind but the query must be missing something (.5 games not showing up) (since not all teams always have same game count top team has played 2 games so teams that played 1 are not really 1 back they should be .5) similar
Current Results
Team GP Wins Losses Points PCT GB
A 2 2 0 4 1.000 0.0
B 1 1 0 2 1.000 0.0-Should be 0.5 not 0 (halfback)
C 1 1 0 2 1.000 0.0-Should be 0.5 not 0 (halfback)
D 1 1 0 2 1.000 0.0-Should be 0.5 not 0 (halfback)
E 1 0 1 1 0.000 1.0
F 1 0 1 1 0.000 1.0
G 1 0 1 1 0.000 1.0
H 2 0 2 2 0.000 2.0
My Code
SELECT
Team,
COUNT(*) AS GP,
SUM(is_win) AS Wins,
SUM(NOT is_win) AS Losses,
2 * SUM(is_win) + SUM(NOT is_win) AS Points,
truncate(SUM(is_win) / ((SUM(is_win)+(sum(NOT is_win)))), 3) AS PCT,
truncate((case when Count(*) - (sum(is_win) - sum(NOT is_win)) = 0 then 0
else (abs(Count(*) - (sum(is_win) - sum(NOT is_win))) / 2) end),1)as GB
FROM
(
SELECT
HomeTeam AS Team,
HomeScore > AwayScore AS is_win
FROM Games
UNION ALL
SELECT
AwayTeam AS Team,
HomeScore < AwayScore AS is_win
FROM Games
) T1
GROUP BY Team
ORDER BY Wins DESC, PCT DESC, Losses ASC
August 15, 2014 at 1:10 pm
You seem to be working on something different to SQL Server. I had to translate the code to test the code.
Let's review the rules.
For each win, the team gets 2 points and for each lose it gets 1. At least, that's what's happening.
Ties will be considered as no_wins. As a result, COUNT(*) = (SUM(is_win) - SUM(NOT is_win)).
With those rules, halfbacks won't be shown as abs(Count(*) - (sum(is_win) - sum(ABS( is_win - 1)))) will always be 0.
What happens if a team has played 3 games and others have played just 1?
August 15, 2014 at 1:23 pm
1. You seem to be working on something different to SQL Server. I had to translate the code to test the code. (I am sql from phpMyAdmin a web database)
Let's review the rules.
For each win, the team gets 2 points and for each lose it gets 1. At least, that's what's happening.
Ties will be considered as no_wins. As a result, COUNT(*) = (SUM(is_win) - SUM(NOT is_win)). There are no ties in the league
With those rules, halfbacks won't be shown as abs(Count(*) - (sum(is_win) - sum(ABS( is_win - 1)))) will always be 0.
What happens if a team has played 3 games and others have played just 1?
So if a team goes 3-0 and a team is 1-0 the team that is 1-0 should show 1 game back
Does this make sense....similar to the NBA standings where there are no ties.
August 15, 2014 at 2:40 pm
I could help you get the desired result in SQL Server, however, that might not work for you. You could try on a forum based on MariaDB which is what your DB seems to be on.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply