Jump to content


Photo

Grouping by numbers


  • Please log in to reply
2 replies to this topic

#1 nickspick

nickspick
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 22 September 2006 - 05:04 PM

Hi,

I've got a table with a column that stores prices that vary considerably. These prices will be used to create options for the user to select a price range. Rather than presenting every number I want to group the numbers into ranges and display these along with a count of how many results fall into each range.

I've been able to achieve the same concept with datestamps (See example) but am stumped with what to use for just plain numbers.
[pre]
SELECT
DATE_FORMAT(FROM_UNIXTIME(date), '%Y') as new_date,
COUNT(*) AS numRows
FROM
table
GROUP BY
new_date

[/pre]
The above example will group all timestamps into years and also count how records are contain within each group. I'd like a similar thing for numbers but grouping them into 1000s or 10000s.

Any ideas?

Many thanks!

#2 jvrothjr

jvrothjr
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 22 September 2006 - 05:35 PM

You would want to use the option [between] on your select query statement

$query = "select * from pricing where (Mcov between $Minprice and $Maxprice)";

If you understand everything you know nothing!

http://rcchjr.awardspace.com/

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 02:35 AM

And if you use that with a proper min/max/ceil/floor function, you can get MySQL to dump out the range as well.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users