Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# losing precision in a calculated column

Posted on 2010-08-17
Medium Priority
264 Views
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,

``````
0
Question by:zorba111
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 33454940
``````CAST( SUM(EA.ex_pi_eval_answer_response) as MONEY) / CAST(COUNT(EA.ex_pi_eval_answer_response) AS DECIMAL(20,4))
``````
0

Author Comment

ID: 33455011
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...

0

Author Comment

ID: 33455035
@angell

yours works too, also when simplified to:

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month9 days, 11 hours left to enroll