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 :(

Answers


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

yields

Category  zero-five  five-ten
Cat1        2          4
Cat2        3          6

or

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

yields

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

Need Your Help

Map tiles flicker between terrain and normal types

android google-maps google-maps-android-api-2 google-play-services

I have a Google Maps v2 application. During rendering, the map tiles flicker between terrain-type and normal-type. You can see the tile boundaries in the screenshot below. Each tile seems to flicker

Email Results from Long PHP Script

php email

I need to process a long PHP script and have the results emailed to the user once the script has completed. How is this done?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.