Loading

MS SQL Decimal Place problem. Should be so Simple.

Guest User

13y2.1kSQL

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?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman13y ago
Accepted answer
try this

SELECT Customer, QtyRejected, Year, Month, QtyShipped, Cast(SUM(QtyRejected) aS decimal(18,9)) /CAST( SUM(QtyShipped) AS decimal(18,9)) * 1000000 AS Numeric(9, 2)) AS PPM
FROM dbo.MasterRejects_and_CCARShipped_Join
GROUP BY Customer, QtyRejected, Month, Year, QtyShipped
0
Guest User13y ago
So simple when I think about it.

Thank you once again Santhosh!!
0