[dba-SQLServer] Counts of value ranges

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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com