Bracketing records?

[rjoseph]

Hi Guys

I think this is an easy one for you.

Basically, I want a simple SQL statement which queries my vehicle database. My fields and example data within the fields can be seen below:

"Make", "Model", "Price"
"Porsche", "911", 45000"
"Porsche", "911", 42000"
"Porsche", "911", 58000"
"Porsche", "911", 51000"
"Porsche", "911", 32000"
"Porsche", "911", 28000"
"Porsche", "911", 21000"

Basically, I want to display how many Porsches (or any other car) I have listed within my database by price brackets. So, the results on my page would look something like this:

Porsche Cars within 0 - 2000 price bracket: 0
Porsche Cars within 2001 - 5000 price bracket: 0
Porsche Cars within 5001 - 10000 price bracket: 0
Porsche Cars within 10001 - 15000 price bracket: 0
Porsche Cars within 15001 - 20000 price bracket: 0
Porsche Cars within 20001 - 30000 price bracket: 2
Porsche Cars within 30001 - 40000 price bracket: 1
Porsche Cars within 40001 - 50000 price bracket: 2
Porsche Cars within 50001 - 10000 price bracket: 2
Porsche Cars within 100000 + price bracket: 0

How would I do this and what would my statement look like? Any help would be fully appreciated

Best regards

Rod from the UK

[mattyblah]

query would be something like:

select
sum(case when price between 0 and 2000 then 1 else 0 end) a [0-2000]
, sum(case when price between 2001 and 5000 then 1 else 0 end) as [2001-5000]
, sum(case when price between 5001 and 10000 then 1 else 0 end) as [5001-10000]
, sum(case when price between 10001 and 15000 then 1 else 0 end) as [10001-15000]
, sum(case when price between 15001 and 20000 then 1 else 0 end) as [15001-20000]
, sum(case when price between 20001 and 30000 then 1 else 0 end) as [20001-30000]
, sum(case when price between 30001 and 40000 then 1 else 0 end) as [30001-40000]
, sum(case when price between 40001 and 50000 then 1 else 0 end) as [40001-50000]
, sum(case when price between 50001 and 100000 then 1 else 0 end) as [50001-100000]
, sum(case when price > 100000 then 1 else 0 end) as [100000+]
from table
that should get you the results you need, if you are using sql server. let me know how it goes.

[rjoseph]

Thanks Matty

Your solution worked a treat.

Thanks very much!

Rod from the UK