MS SQL Decimal Place problem. Should be so Simple.

Question

Hi All I have come across an odd problem. SELECT Customer QtyRejected Year Month QtyShipped CAST(SUM(QtyRejected) / SUM(QtyShipped) * 1000000 AS Numeric(9 2)) AS PPM FROM dbo.MasterRejects_and_CCARShipped_Join GROUP BY Customer QtyRejected Month Year QtyShipped The calculation is 45 / 322524 * 1000000. The answer should be 139.52. The SQL server returns 139.00 I cannot see what I am doing wrong. Could someone please help?

Santhosh Kumar Jayaraman · Answer

try this SELECT Customer QtyRejected Year Month QtyShipped Cast(SUM(QtyRejected) aS decimal(189)) /CAST( SUM(QtyShipped) AS decimal(189)) * 1000000 AS Numeric(9 2)) AS PPM FROM dbo.MasterRejects_and_CCARShipped_Join GROUP BY Customer QtyRejected Month Year QtyShipped

Chris Dude · Answer

So simple when I think about it. Thank you once again Santhosh!!