Re: Help SQL question (Calculation)

[David McKee]

Good morning Douglas,

There is never a tie...

I removed the truncate and have an error

New 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,
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


ERROR:
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'when Count(*) - (sum(is_win) - sum(NOT is_win)) = 0 then 0 else (abs(Count(*) - ' at line 8