zorba111
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@angell
yours works too, also when simplified to:
SUM(EA.ex_pi_eval_answer_r esponse) / CAST(COUNT(EA.ex_pi_eval_a nswer_resp onse) AS DECIMAL(20,4))
yours works too, also when simplified to:
SUM(EA.ex_pi_eval_answer_r
ASKER
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_schoo
SUM(CAST(EA.ex_pi_eval_ans
COUNT(EA.ex_pi_eval_answer
SUM(CAST(EA.ex_pi_eval_ans
from tbl_ex_ProgramImpact_eval_
where EA.ex_pi_eval_answer_Progr
group by EA.ex_pi_eval_answer_schoo