Sql Returning 0 where a ratio expected

Question

I have as part of an SQL Query

SELECT
     TransID,
     SUM(isSatisfactory) as SatTr,
     count(AllTrades.AllTradeIndex) as AllTr,
     (SUM(isSatisfactory)  / count(AllTrades.AllTradeIndex) ) * 100 as RatioOf

   FROM
       ...Several subqueries here...
   GROUP BY TransID

The results are

strTransID  SatTr   AllTr   RatioOfGoodAccounts
307319906   6       12        0

If I select the columns without the group and math, this is the result set

  strTransID    isSatisfactory   AllTradeIndex
    307319906    NULL            1334036
    307319906    NULL            1334037
    307319906    NULL            1334038
    307319906    NULL            1334039
    307319906    NULL            1334040
    307319906    1               1334041
    307319906    NULL            1334042
    307319906    1               1334043
    307319906    1               1334044
    307319906    1               1334045
    307319906    1               1334046
    307319906    1               1334047

I thought perhaps the nulls was messing it up, but I tried coalesce and it didn't affect the results.

integer math. change `(SUM(isSatisfactory) / count(AllTrades.AllTradeIndex) )` to `(SUM(isSatisfactory*1.0) / count(AllTrades.AllTradeIndex*1.0) )` in SQL server if the data type is integer (no decimal) being evaluated then the result is also integer(no decimals) to resolve, cast datatypes to decimal of desired length or multiply by a decimal so the engine knows to include decimals in result. [Docs showing this is expected](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql) — xQbert, Oct 10 '17 at 15:16

score 0 · Accepted Answer · answered Oct 10 '17 at 15:22

Both your nominator and denominator are integers, so you're performing integer division, which leaves only the "whole" part, i.e. 0 in this case.

You can use floating point division by explicitly casting the nominator to a floating point type. Or even simpler, since you're multiplying by a numeric literal anyway, just use a floating point literal. Note, however, that it must be used before the division, not after it:

(SUM(isSatisfactory) * 100.0  / COUNT(AllTrades.AllTradeIndex) ) AS RatioOf
-- Here ---------------^

Sql Returning 0 where a ratio expected

1 Answers1