计算特定范围的记录数sql server [英] Counting number of records for specific ranges sql server

问题描述

我试图写一个查询来计算基于多个不同范围的记录数。

我使用

这是我做的：

选择count（col1）作为range1
来自tbl1
其中col1 <= 15000
union
select count（col1）as range2
from tbl1
其中col1> 15001和col1 <= 30000
union
选择计数（col1）作为范围3
来自tbl1
其中col1> 30001和col1 <= 45000
etc ...


我使用sql server 2008像我上面所说的，我是积极的有一个更好的方法来做这个，也许这样： sql计数，

编辑：是的，数据库是sql 2008，工作完全根据需要。我忘了提到我实际上正在读一个通过coldfusion 序列化的 JSON 文件 > serializeJSON 。所以在数据库中，下面的一切都是完美的，但是ColdFusion查询查询不支持 CASE 语句，或者它不出现。

解决方案

一种方法是使用条件求和（针对单独列中的值）：

 选择sum（当col1 <= 15000时为1 else 0结束时的情况）为range1，
 sum（当col1> 15001且col1 < 30000 then 1 else 0 end）as range2，
 sum（case col1> 30001 and col1 <= 45000 then 1 else 0 end）as range3 
 from tbl1; 
    在单独的行上的值）：
  select（case col1 <= 15000 then'range1'
 > 15001和col1 <= 30000，然后是'range2'
，当col1> 30001和col1 <= 45000时，则'range3'
 else'other'
 end） （*）as cnt 
 from tbl1 
 group by（case col1< = 15000 then'range1'
当col1> 15001和col1< = 30000 then'range2'
当col1> 30001和col1< = 45000然后'range3'
 else'other'
 end）; 
  
我经常使用此表单的子查询：
  select（*）
 from（select t。*，
（col1 <= 15000 then'range1'
当col1> 15001且col1 <= 30000时，那么'range2'
，当col1> 30001且col1 <= 45000时，则'range3'
 else'other'
 end） 
 from tbl1 
按范围分组; 
  
 code> range 只出现一次。
 
 
 
 EDIT：
 
 
 
上面的逻辑错过了 15001 和 30001 的值，我的猜测是OP实际上意味着 col1> 15000和col1 <= 30000 和 col1> 30000和col1 <= 45000  for the conditions。但是，我不改变它们，因为上面是原始问题的语句（也许有一些特殊的 15001 和 30001 ）。
 
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. 
解决方案
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).

                        
这篇关于计算特定范围的记录数sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！