# divide by zero

#### longway

##### Member
I have a table that contains football teams, and the number of wins and losses that they have. I am ordering them by win percentage, so they are in order of first place down to last. Here is my query:

select * from nfcnorth
order by won / won + loss

The problem is when a team has no wins. this causes a division by zero, which of course causes an error. what can I do about this?

#### kulrom

##### Well-known member
simply add 1s to the WON's as it wouldn't take effect on final order but will assure that there isn't Null value/s ... i.e. (won+1)/(won+1) + loss

however it will order them properly

Regards

Last edited:

#### jmcilhinney

##### VB.NET Forum Moderator
Staff member
I haven't tested this but I believe it should work:
VB.NET:
``ORDER BY CASE WHEN won + loss = 0 THEN 0 ELSE won / (won + loss)``

#### TechGnome

##### Well-known member
It doesw work, I use that kind of a construct all the time.

as long as it's SQL Server..... I can't vouch for any other DBMS.

-tg

#### kulrom

##### Well-known member
Actually it is kind of math trick ... i made some simple test and i came to this

VB.NET:
``````[size=2][color=#0000ff]Dim[/color][/size][size=2] wins [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Double[/color][/size][size=2] = Val(txt1.text)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] loss [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Double[/color][/size][size=2] = Val(txt2.Text)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] result [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Double

[/color][/size][size=2][color=#0000ff]If[/color][/size][size=2] wins = 0 [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]result = 0

[/size][size=2][color=#0000ff]Else

[/color][/size][size=2]result = wins / (wins + loss)

[/size][size=2][color=#0000ff]End [/color][/size][size=2][color=#0000ff]If

[/color][/size][size=2]MessageBox.Show(result)``````
[/size]
and finally you can implement as it follows:
VB.NET:
``select * from nfcnorth order by result``
with a difference that you should first fetch wins instead enter in textBox like i did here

Regards

Replies
4
Views
919
Replies
7
Views
2K
Replies
0
Views
2K
Replies
0
Views
823
Replies
0
Views
4K