自动计数(*)

Question

我有一个查询，我试图确定在特定的日子里发生的事件的百分比，我只得到0。我认为(但我不确定)是什么原因导致我的查询四舍五入。这种情况发生在Server中，而不是MySQL中。

/* create the event table */
create table event (id int
                  , dayOf datetime
                  , description varchar(32)
);

/* add some events */
insert into event( id, dayOf, description ) values
( 1, '2018-01-01', 'Thing 1'),
( 2, '2018-01-01', 'Thing 2'),
( 3, '2018-01-02', 'Thing 3'),
( 4, '2018-01-02', 'Thing 4'),
( 5, '2018-01-03', 'Thing 5');

/* try to get % of events by day, but actually get zeroes */
select event_daily.dayOf, event_daily.cnt, event_total.cnt, 
       event_daily.cnt / event_total.cnt as pct_daily /* this is the zero */
from ( select dayOf, count(*) as cnt from event group by dayOf ) event_daily
   , ( select count(*) as cnt from event ) event_total;

预期结果：

DateOf    cnt  cnt  pct_daily
1/1/2018   2    5    0.40
1/2/2018   2    5    0.40
1/3/2018   1    5    0.20

实际结果：

DateOf    cnt  cnt  pct_daily
1/1/2018   2    5     0
1/2/2018   2    5     0
1/3/2018   1    5     0

任何帮助都将不胜感激！

Answer 1

这是因为Server执行整数除法，所以可以首先使用CAST将其转换为浮点数

select event_daily.dayOf, event_daily.cnt, event_total.cnt, 
       CAST(event_daily.cnt AS float) / CAST(event_total.cnt AS float) as pct_daily 
from ( select dayOf, count(*) as cnt from event group by dayOf ) event_daily
   , ( select count(*) as cnt from event ) event_total;

Answer 2

尝试以下方法

declare @TotalCount DECIMAL(18, 2)
select @TotalCount =  count(*) from #event

select
a.dayOf, a.DailyCount, a.TotalCount, CONVERT(DECIMAL(18, 2), (A.DailyCount/A.TotalCount)) AS pct_daily
FROM
(select 
    dayOf, Count(Id) AS  DailyCount, @TotalCount as TotalCount
from 
    #event
group by
    dayOf ) a