CODEWITHSUNDEEP
sqlsql-servert-sql
Kyle G
Kyle G

Reputation: 149

How to CAST to decimal without rounding in TSQL

Using this SQL statement:

SELECT COUNT(DISTINCT[User].Id) AS UserTableCount, 
COUNT(QA.RequestID) AS QACount, 
QA.AssignedToUser, 
CAST((COUNT(QA.RequestID)/COUNT(DISTINCT [User].Id)) AS DECIMAL(10,4)) AS PercentOf 
FROM [User]  
JOIN [QA] ON [User].Id = [QA].RequestId 
WHERE QAStatus = 'Pink' GROUP BY [QA].AssignedToUser

I get these results: enter image description here

How do get a 2 decimal space result that isn't rounded?

Upvotes: 1

Views: 12902

Answers (2)

C B
C B

Reputation: 1717

SQL is performing the division using integer datatypes. Multiply one of the inputs that you are using for division by 1.00.

Ex.

CAST(((COUNT(QA.RequestID)*1.00)/COUNT(DISTINCT [User].Id)) AS decimal(10,2))

In this situation:

CAST((COUNT(QA.RequestID)/COUNT(DISTINCT [User].Id)) AS DECIMAL(10,4))

the result is not what you would hope to receive because the integer division is performed before the cast operation.

Upvotes: 4

Tab Alleman
Tab Alleman

Reputation: 31785

Cast both sides of the division operator to decimal.

You are doing integer division which produces an integer result, and then converting that integer to a decimal, which is going to have zeros behind the decimal point like all good integers do.

Upvotes: 3

Related Questions