Calculations in stored procedure

[ds7387]

I need help with my stored procedure. I am trying to perform a calculation query. My table looks like this:

Table Name: GameStats
Composite Primary Key: GameID and PlayerID
Fields: AtBats, Hits

I am trying to calculate the batting average for each player on my baseball team. Batting average is Hits divided by AtBats. So I need to total up the number of hits and then total up the number of at bats for all the games and then calculate the batting average.

Your help is greatly appreciated.

Youth Baseball Coach

 

[cathyw]

I'm not in a place where I can test this, but how about this for the select statement:

SELECT PlayerID, BatAvg = SUM(Hits)/SUM(AtBats)
FROM GameStats
GROUP BY PlayerID

This should list all player ids and their corresponding average based on all games in the table.

Hope that helps,
Cathy

 

[ds7387]

Thanks for the quick reply. I will try it out tonight. Can you also answer for me this question?

1. My Hits field and AtBats field are of type Int. When you divide an integer by an integer, are you going to get an integer result. What can I use to make sure I get a decimal result.

2. Also, let's say that you have 4/8 = .5

Is there any way to make the result look like this .500 and go all the way to the thousandths place.

3. Also, what if the result of a calculation is .437947. How can I round to get an answer of .438?

Thanks again for your help.

Youth Baseball Coach

 

[cathyw]

You're right, if hits and at bats are integer, then the result will be integer. I'm not sure, but you may be able to use the cast or convert function to convert these to numeric or decimal.

As for the rounding/formatting, I think there is a Round function that could help. However, depending on how you're using the results of your stored procedure, you may want to deal with these kinds of issues where you are displaying the data instead. Just a thought.

Also, in thinking about this more, you should add the following to the end of the earlier select statement (after the group by):
HAVING SUM(AtBats) <> 0
This will prevent a &quot;division by zero&quot; error from occurring if a player happened to get into the table with zero at bats.

 

[tlbroadbent]

Here is an easy way to convert the result to decimal.

SELECT PlayerID, BatAvg = 1.0 * SUM(Hits)/SUM(AtBats)
FROM GameStats
WHERE AtBats>0
GROUP BY PlayerID

To obtain a fixed number of decimals use CAST or CONVERT.

SELECT
PlayerID,
BatAvg = Cast(1.0 * SUM(Hits)/SUM(AtBats) As Decimal(5,3))
FROM GameStats
WHERE AtBats>0
GROUP BY PlayerID If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm