I'm using VBScript (ASP Classic) and SQL Server; I'm trying to have a section on the website where you can see a count of products at certain price levels. Something like:
$50 - $100 (4)
$100 - $500 (198)
$500 - $1000 (43)
For the love of me I cannot figure out a good way to do a query like this. I mean... I know how to get the number of products at a certain price range, but I can't figure out how to do it at several different price ranges without having to write a boatload of nested queries to aggregate all the results, especially since the price ranges can differ greatly among related products.
Is there a "rule of thumb" when doing something like this as to how you figure out the different price ranges?
The most efficient way of doing this in SQL (I believe) is to use a sum case construct:
select sum(case when Price >= 50 and Price < 100 then 1 else 0 end) as 50to100,
sum(case when Price >= 100 and Price < 500 then 1 else 0 end) as 100to500,
sum(case when Price >= 500 and Price < 1000 then 1 else 0 end) as 500to1000
from Products
This will only require a single scan of the table.