How can I convert the division of two integer values to a decimal using T-SQL against a iSeries AS/400 Database?

Assuming the follo开发者_如何学Cwing query:

SELECT
    ID,
    COUNT(1) AS NumRecords,
    SUM(Quantity) AS TotalQty
    SUM(Quantity)/COUNT(1) AS Avg
FROM        SOME_TABLE
GROUP BY    ID

Right now it returns:

ID    NumRecords    TotalQty    Avg
1     15            6           2

I want it to return a decimal value with a Scale of 2 for Avg (i.e. "2.5").

I've tried to CAST the calcluation as a DECIMAL, NUMERIC, FLOAT, and VARCHAR, but it always returns an INTEGER.

You need to cast the inputs to the calculation not the result. Also any reason you aren't using the AVG function?

AVG(CAST(Quantity as decimal(10,2)))

An alternative solution is via implicit Casting. I found this to be much cleaner SQL as well. The precision will be determined by the number of trailing zeros used when multiplying by 1.

AVG(Quantity * 1.00)   //x.xx
AVG(Quantity * 1.0000) //x.xxxx