Counting number of records for specific ranges sql server

Question

I am trying to write a query to count the number of records based on a number of different ranges.

I have success with using union, but I feel there is a better way to do it.

Here is what I've done:

select count(col1) as range1
from tbl1
where col1 <= 15000
union
select count(col1) as range2
from tbl1
where col1 > 15001 and col1 <= 30000
union
select count(col1) as range3
from tbl1
where col1 > 30001 and col1 <= 45000
etc...

I am using sql server 2008. Like I stated above, I'm positive there is a better way to do this, maybe something like this: sql count,

EDIT: Yes, the database is sql 2008, and the answers below work exactly as needed. I forgot to mention that I'm actually reading a JSON file that has been serialized via coldfusion serializeJSON. So in the db, everything below worked perfectly, but coldfusion query of queries doesn't support the CASE statement, or it doesn't appear to.

Answer 1

One way is with conditional summation (for the values in separate columns):

select sum(case when col1 <= 15000 then 1 else 0 end) as range1,
       sum(case when col1 > 15001 and col1 <= 30000 then 1 else 0 end) as range2,
       sum(case when col1 > 30001 and col1 <= 45000 then 1 else 0 end) as range3
from tbl1;

Another way is with group by (for the values on separate rows):

select (case when col1 <= 15000 then 'range1'
             when col1 > 15001 and col1 <= 30000 then 'range2'
             when col1 > 30001 and col1 <= 45000 then 'range3'
             else 'other'
        end) as range, count(*) as cnt
from tbl1
group by (case when col1 <= 15000 then 'range1'
               when col1 > 15001 and col1 <= 30000 then 'range2'
               when col1 > 30001 and col1 <= 45000 then 'range3'
               else 'other'
          end);

I often use a subquery for this form:

select range, count(*)
from (select t.*,
             (case when col1 <= 15000 then 'range1'
                   when col1 > 15001 and col1 <= 30000 then 'range2'
                   when col1 > 30001 and col1 <= 45000 then 'range3'
                   else 'other'
              end) as range
from tbl1
group by range;

That way, the definition of range only appears once.

EDIT:

The above all use the logic from the OP. However, the above logic misses the values of 15001 and 30001. My guess is that the OP really means col1 > 15000 and col1 <= 30000 and col1 > 30000 and col1 <= 45000 for the conditions. But, I'm not changing them because the above is how the original question is phrased (perhaps there is something special about 15001 and 30001).

Answer 2

Personally I prefer using a derived (or physical) table to store my range boundaries which I then join back to in order to find my results.

I reckon the code is simpler and easier to extend if required

Something a little like this:

; WITH ranges (lbound, ubound) AS (
  SELECT 0, 1500
  UNION ALL SELECT 1500, 3000
  UNION ALL SELECT 3000, 4500
)
SELECT ranges.lbound
     , ranges.ubound
     , Count(your_table.value) As turtle
FROM   ranges
 LEFT
  JOIN your_table
    ON your_table.value >= ranges.lbound
   AND your_table.value <  ranges.ubound
GROUP
    BY ranges.lbound
     , ranges.ubound