Group by different Price ranges

[imranlink]

Hi,

I want to count total items in my products table according to different price ranges in a single SQL Query.

---

Price_Range (Found_Items)

• Under $100 (900)

• $100-$500 (800)

• $500-1000 (700)

• $1000-$2000 (600)

• $2000-$3000 (500)

• $3000 and above (400)

Table have two colums item #, price.

Can you please help me what MYSQL query should i write to get above data ?

[bastien]

use the case when then construct to do this with sql. The other option is to use code to have some running counters to total the count up

[Sxooter]

Something like this:

create table a (id serial primary key, amount numeric(10,2));
insert into a (amount) values (100);
insert into a (amount) values (10.5);
insert into a (amount) values (45.93);
insert into a (amount) values (45.9233);
insert into a (amount) values (142.23);
insert into a (amount) values (242.23);
insert into a (amount) values (342.23);
insert into a (amount) values (342.23);
insert into a (amount) values (352.23);
insert into a (amount) values (372.23);
insert into a (amount) values (172.23);

select * from a;
 id | amount
----+--------
  1 | 100.00
  2 |  10.50
  3 |  45.93
  4 |  45.92
  5 | 142.23
  6 | 242.23
  7 | 342.23
  8 | 342.23
  9 | 352.23
 10 | 372.23
 11 | 172.23
(11 rows)

select count(id),min(amount),max(amount) from a group by (floor(amount/100));
 count |  min   |  max
-------+--------+--------
     3 |  10.50 |  45.93
     3 | 100.00 | 172.23
     1 | 242.23 | 242.23
     4 | 342.23 | 372.23

That get you closer?