What Is Wrong with This SQL Server Query Division Calculation

What is wrong with this SQL Server query division calculation?

It's because you are doing integer division. You should convert one of the operands to float, or decimal (depending on the precision and purpose of the calculation you are doing), using something like:

((CAST((a+ b + c) AS FLOAT) / 3) / 216647 * 10)

or possibly:

(((a+ b + c) / 3.0) / 216647.0 * 10)

SQL division giving wrong answer

The answer is simple, most likely the data type of the columns EnteredACC and totalCalls is INT (or SMALLINT, BIGINT,etc), therefore it your result is an INT. So, 14.58 becomes 14 and 0.14 becomes 0. You need to do a cast (implicit or explicit) to your datatype:

SUM(CAST(EnteredACC AS DECIMAL(16,4))/SUM(totalCalls)

SQL Server, division returns zero

Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation:

SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);

Why is my calculating a percentage not working correctly when I am dividing?

The problem is integer division. When both operands are integers, SQL Server produces an integer result: for example, 3/2 yields 1, not 1.5.

You need to force decimal context for the operation, for example by multiplying with a (dummy) decimal value, like:

1.0 * b.TY_Sales_USD_LL / b.TY_Qty_LL AS ASP_LL

I would not recommend using isnull(..., 0) for the right operand of a division: if the value is actually null, you get a division by 0, which is a fatal error. On the other hand, dividing by null yields null, which does not fail, and seems more relevant.

Calculating division between two columns SQL

Give this a whirl. I believe it is what you seek.

SELECT
    x.ccode
    , (CAST(ygrade as float)/CAST(xnumber AS FLOAT))*100 AS flow
FROM
(
    SELECT ccode, COUNT(s.ccode) AS xnumber
    FROM Studied s
    GROUP BY ccode
) x
    JOIN
    (
        SELECT ccode, COUNT(grade) as ygrade
        FROM Studied s
        WHERE grade <> 'U'
        GROUP BY ccode
    ) y
        on x.ccode = y.ccode
;

How to avoid the divide by zero error in SQL?

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

But here is a much nicer way of doing it:

Select dividend / NULLIF(divisor, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

---

Related Topics