title: SQL for top 5 and summarizing 'All others' not working ('All others' returning null/0)
body: <p>I'm using SQL in Databricks (not sure if it makes a difference), and I'm trying to run a report to provide the top 5 field values by record counts, and then to provide an 'All Other' line with the record counts of all other records than the top 5.  </p>
  
  <p>It's working well except that the 'All Other' line is returning null/0 for values.  When I run the code that is doing the 'All Other' line alone against the entire table, it works as expected. </p>
  
  <p>Running the problem part of the query by itself.  It works.  So I can't figure out why it isn't working as a piece of the larger query.</p>
  
  <pre><code>with top5 as 
    (
      select * 
      from (
            select  link_location_journey
                  , rec_count
                  , 1 as unique_values
                  , row_number() over( order by rec_count DESC) as location_rank
            from adobe_scratch_work.z_barb_test_3
            )
      ranks
      where location_rank &lt;= 5
    )
  
    select *  from top5
  
    union all
  
    select 'All Other' as link_location_journey
         , sum(rec_count) as rec_count
         , count(*) as unique_values
         , 6 as location_rank
  
   from adobe_scratch_work.z_barb_test_3
  
   where link_location_journey not in
       (select link_location_journey from top5)
  </code></pre>
  
  <p>The problem is the code under the line with "union all".</p>
  
  <p>This is what I get back now:</p>
  
  <pre><code>link_location_journey    rec_count    unique_values    location_rank
      null                 55,110,080         1               1
      body                 31,804,799         1               2
      content-rail         14,077,267         1               3
      nav-sub-menu          8,183,793         1               4
      nav-top-menu          6,564,720         1               5
      All Other               null            0               6
  </code></pre>
  
  <p>I expected:</p>
  
  <pre><code>link_location_journey    rec_count    unique_values    location_rank
      null                 55,110,080         1               1
      body                 31,804,799         1               2
      content-rail         14,077,267         1               3
      nav-sub-menu          8,183,793         1               4
      nav-top-menu          6,564,720         1               5
      All Other           224,633,332        30               6
  </code></pre>
  
  <p>Because when I run this against the entire table:</p>
  
  <pre><code>     select 'All Other' as link_location_journey
            , sum(rec_count) as rec_count
            , count(*) as unique_values
            , 6 as location_rank
  
       from adobe_scratch_work.z_barb_test_3
  </code></pre>
  
  <p>I get back:</p>
  
  <pre><code>link_location_journey    rec_count    unique_values    location_rank
      All Other            138,203,992        35               6
  </code></pre>
  
  <p>Any idea what I'm doing wrong?</p>