August 31, 2015 at 10:57 am
The total quantity(sum(woitem.qtytarget) as total) result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)
select wo.num, sysuser.username,sum(woitem.qtytarget) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
August 31, 2015 at 11:03 am
chindilog (8/31/2015)
The total quantity(sum(woitem.qtytarget) as total) result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)select wo.num, sysuser.username,sum(woitem.qtytarget) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
Just simply change your query to something like the following in order to convert it to a data type with only two decimals. Ensure that you make it big enough to hold the highest value that you would see from the sum.
select wo.num, sysuser.username,CONVERT(DECIMAL(12,2), sum(woitem.qtytarget)) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply