Accumulating count calculation in SQL Server

Say if I have a table with 2 c开发者_运维百科olumn - ID, money. I want to do a query to find out how many ID have money less than 100, 200, 300, ..., 10000 How should I do this in SQL Server? Will I need to use variables?

Assuming id is a key column, try this:

Select Case When money < 100 Then 'LT100'
            When money < 200 Then 'From100To199'
            When money < 300 Then 'From200To299'
            When money < 1000 Then 'From300To999'
                              Else 'GE1000' End Bucket, 
       Count(*) Count
From Table
Group By Case When money < 100 Then 'LT100'
            When money < 200 Then 'From100To199'
            When money < 300 Then 'From200To299'
            When money < 1000 Then 'From300To999'
                              Else 'GE1000' End  

EDIT: If the length or size of the buckets is a constant, (or can be represented as a SQL Expression) then you can do any arbitrary number of buckets by defining the buckets as the output of that expression, as in, for example, to defined every $100 dollar buckets:

Select Str(Floor(Cast(money / 100)), 8, 0)  Bucket, 
       Count(*) Count
From Table
Group By  Str(Floor(Cast(money / 100)), 8, 0)

or, to have 100 dollar buckets up to $1000, and then $1000 buckets from there on up:

Select Case When Money < 1000 
            Then  Str(Floor(Cast(money / 100)), 8, 0) 
            Else Str(Floor(Cast(money / 1000)), 8, 0) End  Bucket, 
       Count(*) Count
From Table
Group By Case When Money < 1000 
            Then  Str(Floor(Cast(money / 100)), 8, 0) 
            Else Str(Floor(Cast(money / 1000)), 8, 0) End 

to get accumulated counts, I'd use the output of above SQL querys as a subquery in another SQL: Using the first one as example:

  Select LT100,
         LT100 + From100To199 LT200,
         LT100 + From100To199 + From200To299  LT300,
         LT100 + From100To199 +  From200To299 + From300To999  LT1000,
         LT100 + From100To199 +  From200To299 + From300To999 + GE1000 Total     
  From ( Select Case When money < 100 Then 'LT100'
                When money < 200 Then 'From100To199'
                When money < 300 Then 'From200To299'
                When money < 1000 Then 'From300To999'
                                  Else 'GE1000' End Bucket, 
           Count(*) Count
         From Table
         Group By Case When money < 100 Then 'LT100'
                When money < 200 Then 'From100To199'
                When money < 300 Then 'From200To299'
                When money < 1000 Then 'From300To999'
                                  Else 'GE1000' End) Z

Sometimes it's simplest to use a loop in a stored procedure:

CREATE TABLE #tmp0(
bucket int,
accumulator int,
)

/* for(n = maxvalue; n > 0; n -= 100) */

Declare @n int
select @n = (select 100 + (100 * (max(moneyField) / 100)) from tbl)
while @n > 0
begin
    insert into #tmp0 (bucket, accumulator) 
        (select @n, count(*) from tbl where moneyField < @n)
    select @n = @n - 100
end
select * from #tmp0 order by bucket

drop table #tmp0

SELECT
     SUM(CASE WHEN MONEY < 100 THEN 1 ELSE 0 END AS '0-100'
    ,SUM(CASE WHEN MONEY >= 100 AND MONEY < 200 THEN 1 ELSE 0 END AS '100-200'
    ,SUM(CASE WHEN MONEY >= 200 AND MONEY < 300 THEN 1 ELSE 0 END AS '200-300'
FROM
    Table