How should I fix?

Other SQL Server Topics
1

WITH nl_sentcount
AS(
Select nl_id, count(distinct user_id) as sent_num from sheet1$ Where event_type='nlsent' group by nl_id
)
, nl_opencount AS (
SELECT nl_id, count(distinct user_id) as open_num from Sheet1$ where event_type = 'nlpv' group by nl_id
)
select a.nl_id, sent_num, open_num, ROUND(open_num/sent_num, 2) * 100
from (nl_sentcount a
inner join nl_opencount b
on a.nl_id = b.nl_id)

The result look like this.
image
Why will the last column be 0? How should I fix it to get none 0 value?

2 
with src
as
(

select 40 sent_num, 10 open_num
)
select *, open_num * 1.0/sent_num* 1.0, 
ROUND(open_num/sent_num, 2) * 100,
ROUND(open_num*1.0/sent_num* 1.0, 2) * 100 
From src
3 Likes
3

@yosiasz is a champ. He gave you some sample code but didn't explain the apparent problem. I'm guessing because you didn't give the data types of the columns but presumably you are simply falling afoul of integer division, in which 10 / 40 produces 0. The two occurrences of "* 1.0" just get the numbers as float, which is presumably what you want.

2 Likes