[MySQL] Number of items between range

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bizzy
Forum Newbie
Posts: 10
Joined: Mon Apr 16, 2007 1:15 pm

[MySQL] Number of items between range

Post 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.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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
Bizzy
Forum Newbie
Posts: 10
Joined: Mon Apr 16, 2007 1:15 pm

One query

Post by Bizzy »

I hope there is more elegant solution, though thanks for reply.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply