How to create a range of 5 price ranges from a products table
I have a products table with a column for price and master category.
I'd like to query my DB to find the range of prices there are for a given category (or all categories). This range of prices would then be broken into an appropriate number of price-ranges ie £0-£5 £5-£10 etc...
I can't get my head around how to do this; do I need to do this all with php?
EDIT: having had a wee think about it, to clarify, I would like to end up so that whatever category is being listed, I can show perhaps 4 price ranges with roughly equal numbers of products in each. I can't get my feeble mind around it :(
For the min and max price per category:
SELECT category, min(price) as min_price, max(price) as max_price from Products Group BY category
For ranges directly in a query you could do something like this
SELECT category, SUM(CASE WHEN price BETWEEN 0 AND 5 THEN 1 ELSE 0 END) as zero-five, SUM(CASE WHEN price BETWEEN 5 AND 10 THEN 1 ELSE 0 END) as five-ten, ... from Products Group BY category
Category zero-five five-ten Cat1 2 4 Cat2 3 6
SELECT category, range, count(*) FROM ( SELECT category, CASE WHEN price BETWEEN 0 to 5 THEN '0-5' WHEN price BETWEEN 5 to 10 THEN '5-10' ... ELSE '10+' END as range FROM products ) group by category, range
Category range count Cat1 0-5 2 Cat1 5-10 4 Cat1 10+ 0 Cat2 0-5 3 Cat2 5-10 6 Cat2 10+ 0