Dynamic Price Range Break Down of Items

[termn8r]

If I have 50,000+ items in a MySQL database and I want to give the option of the viewer to show the items by price breakdown (like tigerdirect etc). What do I do to make sure I have a good breakdown? I have taken the min and max prices and using float in a calc I can break the price rangers into 5 groups but sometimes a group will have 0 items due to price variances. Other times one group will have 500 items while another group has 1. I also tried to take number of items and divide it by the number of options I want but again it doesn't give me a very good response. I feel like I am missing something. I dont want all the price groups to have the same number of items but I also dont want there to be 500 in one group and 3 in another. I tried requiring 15-25% in each group and this helped some but I feel like there should be a mathematical way do this either in PHP or MySQL that is simple.

 

say min price is $0.10 and max price is $600 but most items in this category are $14. I would want it to do something like

 

$0.10-$10.99 (23)

$11.00-19.99 (31)

$20.00-$500.00 (12)

 

and if min price was $2.99 and max was $22.99

 

$2.99-$5.99 (14)

$6.00-$9.99 (22)

$10.00-$15.99 (12)

$16.00-$22.99 (5)

 

where minimum price range groupings is 3 and max is 5 (I am showing 3 and 4 in my examples)

 

Thanks in advance for any help on this. I have been working on it for several days now with minimal results.