CODEWITHSUNDEEP
sql-server
Mike PG
Mike PG

Reputation: 231

Count(*) automatically rounds

I have a query where I am trying to determine what percentage of events happen on certain days and I'm getting nothing but zeroes back. I think (but am not sure) that something is causing my query to round. This is happening to me in SQL Server but not 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;

Anticipated result:

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

Actual result:

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

Any help would be much appreciated!

Upvotes: 2

Views: 72

Answers (2)

Jun Rikson
Jun Rikson

Reputation: 1884

That is because SQL Server performs integer division, you can convert it into float first with 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;

Upvotes: 2

Ayyappa.k
Ayyappa.k

Reputation: 1

Try the below approach

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

Upvotes: 0

Related Questions