How to create a request like "filter by price"?

Question

I am using VBScript (ASP Classic) and SQL Server; I am trying to have a section on a website where you can see the number of products at certain price levels. Sort of:

$50 - $100 (4)
$100 - $500 (198)
$500 - $1000 (43)

For the love of me, I cannot find a good way to make such a request. I mean ... I know how to get the number of products in a certain price range, but I can’t figure out how to do this in several different price ranges, without having to write a boat from sub-queries to aggregate all the results, especially because price ranges can vary greatly between related products.

Is there a "thumb rule" when you do something like this, how do you define different price ranges?

Answer 1

The most efficient way to do this in SQL (I believe) is to use a construct sum case:

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 require only one table scan.

Answer 2

The table brought him:

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 categories of goods:

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 need to add some cleanup and handle the end of the ranges.

Answer 3

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 4

(UDF),

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

.

.

Answer 5

, , SQL, , .