title: Teradata gets division wrong?
body: <p>I have a coworker that doesn't want to include null rows in a percentile rank. The default Teradata function seems to just treat null as the lowest number in the set, so I decided to do the math manually. I started using the below query to test out my equation</p>
  
  <pre><code>drop table tmp;
  
  create multiset volatile table tmp (
    num byteint
  ) primary index (num)
    on commit preserve rows
  ;
  
  insert into tmp
  values (1)
  ;insert into tmp
  values (2)
  ;insert into tmp
  values (1)
  ;insert into tmp
  values (4)
  ;insert into tmp
  values (null)
  ;insert into tmp
  values (4)
  ;insert into tmp
  values (null)
  ;insert into tmp
  values (2)
  ;insert into tmp
  values (9)
  ;insert into tmp
  values (null)
  ;insert into tmp
  values (10)
  ;insert into tmp
  values (10)
  ;insert into tmp
  values (11)
  ;
  
  select
    num,
    case
      when num is null then 0
      else cast(dense_rank() over (partition by case when num is not null then 1 else 2 end order by num) as number)
    end as str_rnk,
    q.nn,
    str_rnk/q.nn as pct_rnk
  from tmp
  cross join (
      select cast(count(num) as number) as nn from tmp
  ) q
  order by num
  ;
  </code></pre>
  
  <p>So what I expect to see in result set is this:</p>
  
  <pre><code>num   str_rnk  nn  pct_rnk
  null        0  10        0
  null        0  10        0
  null        0  10        0
     1        1  10      0.1
     1        1  10      0.1
     2        2  10      0.2
     2        2  10      0.2
     4        3  10      0.3
     4        3  10      0.3
     9        4  10      0.4
    10        5  10      0.5
    10        5  10      0.5
  </code></pre>
  
  <p>But I'm getting a result that looks like it did a regular <code>rank</code> instead of a <code>dense_rank</code>, like this:</p>
  
  <pre><code>num   str_rnk  nn  pct_rnk
  null        0  10        0
  null        0  10        0
  null        0  10        0
     1        1  10      0.1
     1        1  10      0.1
     2        2  10      0.3
     2        2  10      0.3
     4        3  10      0.5
     4        3  10      0.5
     9        4  10      0.7
    10        5  10      0.8
    10        5  10      0.8
  </code></pre>
  
  <p>I know I could set the rank in a subquery and It would calculate the way I expect it to, but why isn't it doing it the way I have it now?</p>