Retrieve products by groups i.e. by price range

[lordspace]

I am trying to retrieve products by groups, i.e. by price range: 0-10; 10.01-19.99

Is there another/better way to do that ?

SELECT CONCAT('$0-$9.99', ' (', count(*), ')') as price_range FROM `PREFIX_products` WHERE sale_price between 0 and 9.99
UNION ALL
SELECT CONCAT('$10-$19.99', ' (', count(*), ')') as price_range FROM `PREFIX_products` WHERE sale_price between 10 and 19.99

[kaykays]

You can try CASE, some thing like this:

PHP Code:

SELECT 
CASE sale_price
    WHEN sale_price between 0 and 9.99 THEN '$0-$9.99'
    WHEN sale_price 10 and 19.99 THEN '$10-$19.99'
END AS pricegroup, 
COUNT(*)
FROM `PREFIX_products`
GROUP BY pricegroup 


[lordspace]

Thanks for the suggestion.

I've modified it a little bit and here are two of the solutions.

Note: When storing FLOAT values in DB be aware that values may not be represented as we think e.g. 3.40 can be internally represented as 3.399999999 and the different can be seen at the very last digit.

Here is a nice explanation and we all should be aware of that:
http://dev.mysql.com/doc/refman/5.0/...ith-float.html

Select products count for given price range

SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 0 and 9.9999
UNION ALL
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 10 and 19.9999
UNION 
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 20.00 and 29.9999
UNION
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 30 and 39.9999
UNION 
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 40 and 49.9999
UNION
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 50 and 99.9999
UNION
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 100 and 149.9999
UNION 
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 150 and 299.9999
UNION 
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price between 300 and 499.9999
UNION 
SELECT count(*) as price_group FROM `PREFIX_products2` WHERE sale_price > 500;

SELECT
CASE
    WHEN sale_price BETWEEN 00 and 09.9999 THEN 1
    WHEN sale_price BETWEEN 10 and 19.9999 THEN 2
    WHEN sale_price BETWEEN 20 and 29.9999 THEN 3
    WHEN sale_price BETWEEN 30 and 39.9999 THEN 4
    WHEN sale_price BETWEEN 40 and 49.9999 THEN 5
    WHEN sale_price BETWEEN 50 and 99.9999 THEN 6
    WHEN sale_price BETWEEN 100 and 149.9999 THEN 7
    WHEN sale_price BETWEEN 150 and 299.9999 THEN 8
    WHEN sale_price BETWEEN 300 and 499.9999 THEN 9
    WHEN sale_price > 500 THEN 10
	ELSE 0
END AS price_group, count(*) as group_count
FROM
	`PREFIX_products2`
GROUP BY
	price_group
ORDER BY
	price_group