List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 31 2001 1:57am
Subject:Re: Divide by 0 in a query ?
View as plain text  
At 7:46 PM -0600 10/30/01, Camilo Rostoker wrote:
>Hi.
>
>I have a query that determines the ranking of teams in a league by
>several calculations on the table.  Below is the query:
>
>SELECT TeamName,TeamWins,TeamLosses,TeamSpirit,
>(TeamSpirit/(TeamWins+TeamLosses)*100) AS AvgSpirit,
>((TeamWins*2)-TeamLosses) AS TeamPoints
>FROM Teams_1
>WHERE DivisionName = 'A Division'
>AND LeagueID = 1
>ORDER BY TeamPoints,AvgSpirit
>
>But the newly created attribute, AvgSpirit, will fail when a team has
>not won or lost a game ...
>
>Any ideas on how to remedy this problem?

Change:

(TeamSpirit/(TeamWins+TeamLosses)*100) AS AvgSpirit,

to:

IFNULL((TeamSpirit/(TeamWins+TeamLosses)*100),0) AS AvgSpirit,


>
>Thanks for the help,
>Camilo Rostoker

Thread
Divide by 0 in a query ?Camilo Rostoker31 Oct
  • Re: Divide by 0 in a query ?Paul DuBois31 Oct