两个数字相除返回 0

2022-01-09 00:00:00 sql sum sql-server

我正在尝试除以 2 个计数以返回一个百分比.

I'm trying to divide 2 counts in order to return a percentage.

以下查询返回 0:

select (
    (select COUNT(*) from saxref..AuthCycle
         where endOfUse is null and addDate >= '1/1/2014') / 
    (select COUNT(*) from saxref..AuthCycle 
         where addDate >= '1/1/2014')
    ) as Percentage

我应该应用演员表吗?

推荐答案

我会做不同的事情，使用两个 sums:

I would do it differently, using two sums:

select sum
       ( case
         when endOfUse is null and addDate >= '1/1/2014'
         then 1
         else 0
         end
       )
       * 100.0 -- if you want the usual 0..100 range for percentages
       /
       sum
       ( case
         when addDate >= '1/1/2014'
         then 1
         else 0
         end
       )
       percentage
from   saxref..AuthCycle

相关文章