SQLTeam.com | Weblogs | Forums

How should I fix?

WITH nl_sentcount
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.
Why will the last column be 0? How should I fix it to get none 0 value?

with src

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

@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.