How do I create a "filter by price" type of query?

Question

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?

Answer 1

You could use union queries

SELECT '100 - 200', COUNT(*) FROM Products
WHERE Price >= 100 AND Price <= 200
UNION
SELECT '300 - 400', COUNT(*) FROM Products
WHERE Price >= 300 AND Price <= 400

Answer 2

Create a function (UDF) that relates price range with a value

$50 - $100 = 1
$100 - $500 = 2
$500 - $1000 = 3

etc.

Then do a select grouping by the function on the value column.

Answer 3

I'd probably use a SQL function which assigned each record with a category code based on the value, and then group by the category code with a count.

Answer 4

Table drive it:

SELECT pr.MinPrice, pr.MaxPrice, COUNT(*)
FROM Products AS p
INNER JOIN PriceRanges AS pr
    ON p.UnitPrice BETWEEN pr.MinPrice AND pr.MaxPrice
GROUP BY pr.MinPrice, pr.MaxPrice
ORDER BY pr.MinPrice, pr.MaxPrice

If you need different price ranges for different product categories:

SELECT pr.ProductCategory, pr.MinPrice, pr.MaxPrice, COUNT(*)
FROM Products AS p
INNER JOIN PriceRanges AS pr
    ON p.ProductCategory = pr.ProductCategory
    AND p.UnitPrice BETWEEN pr.MinPrice AND pr.MaxPrice
GROUP BY pr.ProductCategory, pr.MinPrice, pr.MaxPrice
ORDER BY pr.ProductCategory, pr.MinPrice, pr.MaxPrice

You will have to add some cleanup and handle the end of the ranges.

Answer 5

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.