[dba-SQLServer] Counts of value ranges

Thanks for that answer James.  A poster over on the AccessD list 
provided a method which uses a little table to hold the ranges, which I 
used and which works great.

 > SELECT MinValue, MaxValue, (SELECT Count(*) FROM tblDataTable WHERE
 > Value >=T1.MinValue AND Value <=T1.MaxValue) FROM tblRanges AS T1

Thanks again for your reply.  I keep forgetting about the case 
statement, never having used it.

James Barash wrote:
> John:
> 
> You need to use a Case statement.  To translate your query you need
> something like:
> 
> Select qrptBilling.Bill, (case when [Bill]<10 then 1 else 0 end) as LT10,
> (case then [Bill] < 20 and [Bill]>=10 then 1 else 0 end) as GTE10LT20 from
> qrptBilling
> 
> For your original question, you could use:
> 
> Select sum((case when [Bill]>=2000000 then 1 else 0 end)) as GR2M, sum((case
> when [Bill]>=1000000 and [Bill]<2000000 then 1 else 0 end)) as 1Mto2M, etc.
> from qrptBilling
> 
> That should at least point you in the right direction.
> 
> James Barash
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, April 24, 2008 10:02 AM
> To: Discussion concerning MS SQL Server
> Subject: Re: [dba-SQLServer] Counts of value ranges
> 
> I just wrote a query in Access:
> 
> SELECT qrptBilling.Bill, [Bill]<10 AS LT10, [Bill]<20 And [Bill]>=10 AS 
> GTE10LT20 FROM qrptBilling;
> 
> When I try to do the same thing in SQL Server it promptly wraps the 
> [Bill]<10 in single quotes and treats it as a string:
> 
> SELECT qrptBilling.Bill, '[Bill]<10' AS LT10, '[Bill]<20' And 
> '[Bill]>=10' AS GTE10LT20 FROM qrptBilling;
> 
> Obviously not what I am trying to get at.  So how does one accomplish 
> turning a value comparison into a boolean in SQL Server?
> 
> jwcolby wrote:
>> Guys,
>>
>> Is there a way in SQL to get counts of records in value ranges:
>>
>> $2,000,000+
>> $1 - $1.99M
>> $750K - $999K
>> $500K - $749K
>> $400K - $499K
>> $300K - $399K
>> $200K - $299K
>> $100K - $199K
>> <$100K
> 

-- 
John W. Colby
www.ColbyConsulting.com