Thursday 9 June 2011

SQL syntax for banded count of counts

This is a simple example of SQL syntax for banded count of counts (works on Teradata 12.0)


select case when a.num_occ > 10000 then 'a. > 10000'
                      when a.num_occ > 5000    then 'b. > 5000'
                      when a.num_occ > 2000    then 'c. > 2000'
                      when a.num_occ > 1000    then 'd. > 1000'
                      when a.num_occ > 500     then 'e. > 500'
                      when a.num_occ > 250     then 'f. > 250'
                      when a.num_occ > 100     then 'g. > 100'
                      when a.num_occ > 50      then 'h. > 50'
                      when a.num_occ > 10      then 'i. > 10'
                      when a.num_occ > 1       then 'j. >1'
                      else 'k. = 1'
                      end
            , count(*), sum(a.num_occ)
from (select id, count(*) as num_occ
          from sandbox.events
          group by id) a
group by 1
order by 1 desc

No comments: