Page 1 of 1

[MySQL] Number of items between range

Posted: Tue Nov 13, 2007 1:12 pm
by Bizzy
Hey guys,

can you help me with following problem?

Code: Select all

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `price` int(11) default NULL,
  PRIMARY KEY  (`pid`)
)
I need to get results like this:

Code: Select all

price range | number of items
0-50 | 0
0-100 | 6
0-150 | 7
0-200 | 8
Thanks for help.

Posted: Tue Nov 13, 2007 2:43 pm
by Zoxive
Look at Mysql Keyword Between.

Code: Select all

Select count(id) from `items` where `price` Between '0' and '50'
If you really want it all in one query, you can use subquery's.

Code: Select all

Select (Select count(id) from `items` where `price` Between '0' and '50') as low, (Select count(id) from `items` where `price` Between '0' and '100') as mid, (Select count(id) from `items` where `price` Between '0' and '150') as high

One query

Posted: Wed Nov 14, 2007 11:05 am
by Bizzy
I hope there is more elegant solution, though thanks for reply.

Posted: Wed Nov 14, 2007 11:11 am
by feyd
Divide the price by 50 (and force it to a truncated integer). Grouping the adjusted prices will create a histogram of each segment. You can sum them in PHP afterward.