你要小數點~
要先一開始就轉換~才會得到~
select 生產線
,(
case when text=OK
then 1
else 0
end
) as OK數量
,(
case when text=NG
then 1
else 0
end
) as NG數量
,(
Convert(decimal(18, 2),Convert(decimal(18, 2), OK數量) / (Convert(decimal(18, 2),NG數量) + Convert(decimal(18, 2),OK數量)))
) as 成功率
FROM
select 生產線,OK數量,NG數量,OK數量/(NG數量+OK數量)成功率
from (select 生產線,count(生產線) OK數量,0 NG數量
from TABLE
WHERE text=OK
group by 生產線
union all
select 生產線, 0 OK數量,count(生產線) NG數量
from TABLE
WHERE text=NG
group by 生產線
)
group by 生產線