losing precision in a calculated column

The below query is an example of something I'm struggling with. It produces output like the following:

2052035      10      5      2
5036064      12      5      2
4016002      16      5      3
1010001      23      5      4
5031669      13      5      2
5031163      21      5      4

As you can see from the query, the last column is a calculated column which should give the 2nd column divided by the 3rd column.

I am trying to get the last column to give the exact DECIMAL amount, not an INTEGER. This is why I am trying to cast it etc.

How can I get the exact amount, ie. a decimal type here in this column, not an int ?

We are using SQL Server 2000
select EA.ex_pi_eval_answer_school_id, 
SUM(EA.ex_pi_eval_answer_response), 
COUNT(EA.ex_pi_eval_answer_response),
CAST(SUM(EA.ex_pi_eval_answer_response) / COUNT(EA.ex_pi_eval_answer_response) AS MONEY)

from tbl_ex_ProgramImpact_eval_answers EA

where EA.ex_pi_eval_answer_ProgramObjective_id = 1

group by EA.ex_pi_eval_answer_school_id

Open in new window

zorba111Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
try this, please
CAST( SUM(EA.ex_pi_eval_answer_response) as MONEY) / CAST(COUNT(EA.ex_pi_eval_answer_response) AS DECIMAL(20,4))

Open in new window

0
 
zorba111Author Commented:
Got it!!
This works... I remembered from somewhere that the default is to take the type of the first argument of any operator, and cast the result of an expression to that...

select EA.ex_pi_eval_answer_school_id,
SUM(CAST(EA.ex_pi_eval_answer_response AS DECIMAL)),
COUNT(EA.ex_pi_eval_answer_response),
SUM(CAST(EA.ex_pi_eval_answer_response AS DECIMAL)) / COUNT(EA.ex_pi_eval_answer_response)

from tbl_ex_ProgramImpact_eval_answers EA

where EA.ex_pi_eval_answer_ProgramObjective_id = 1

group by EA.ex_pi_eval_answer_school_id
0
 
zorba111Author Commented:
@angell

yours works too, also when simplified to:

SUM(EA.ex_pi_eval_answer_response) / CAST(COUNT(EA.ex_pi_eval_answer_response) AS DECIMAL(20,4))
0
All Courses

From novice to tech pro — start learning today.