• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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
0
divi2323
Asked:
divi2323
  • 3
1 Solution
 
Renante EnteraCommented:
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 EnteraCommented:
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 EnteraCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now