MySQL Forums
Forum List  »  Newbie

Re: Help SQL question (Calculation)
Posted by: David McKee
Date: August 19, 2014 06:57AM

There is never a tie.

I removed the truncate but get 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

Options: ReplyQuote


Subject
Written By
Posted
Re: Help SQL question (Calculation)
August 19, 2014 06:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.