specifying a conditional statement inside a select statement

I keep baseball statistics in a few tables.  SQL 2000 running on Windows Server 2003 sp1.

I am writing a stored procedure which will output a box score for the game. the problem is that if a person in the game has 0 at bats, a divide by zero error occurs as that is how it is calculated.

What I want to do in my select statement, is IF their at bats is 0, then return 0, otherwise calculate the batting average and return that value instead.

so I guess some pseudocode would be:

SELECT AtBats
      , Hits
      , IF AtBats = 0
           BEGIN
               return 0 for a batting average
           END
        ELSE
           BEGIN
               calculate the batting average and return it
           END
FROM table_stats
LVL 4
divi2323Asked:
Who is Participating?
 
Renante EnteraSenior PHP DeveloperCommented:
Hi divi2323!

Use CASE statement.

It would be this way :

SELECT AtBats
      , Hits
      , CASE AtBats
           WHEN 0 THEN
               0
           ELSE
               -- calculation here
        END
FROM table_stats

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
Renante EnteraSenior PHP DeveloperCommented:
Or some revision with its column name:

SELECT AtBats
      , Hits
      , BatAverage =
      CASE AtBats
        WHEN 0 THEN
          0
        ELSE
          -- calculation here
      END
FROM table_stats

Let me know if you need some explanation.


Regards!
eNTRANCE2002 :-)
0
 
NawalKishore1976Commented:
Use the Case Clause
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi NawalKishore1976!

Is there any difference with what I have posted ???


Regards!
eNTRANCE2002 :-)
0
 
rafranciscoCommented:
You can also try this:

SELECT AtBats
      , Hits
      , ISNULL(Hits / NULLIF(AtBats, 0), 0) as BattingAverage
FROM table_stats
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.