Price filter using mysql

I would like to create a price filter from following table. Currently works with two mysql query first one create price range and second will create count by an ajax call.

Is there any other way to get both in single query.

Expected result:

300- 400 (2)
400- 500 (2)
500- 600 (2)
6开发者_Go百科00- 620 (2)

Lets say this query returns your price range:

SELECT a, b FROM x WHERE ... blah blah blah

Now. If you want to count this is what i would do:

SELECT 
     CONCAT(y.a,'-',y.b) "range",
     (SELECT COUNT(*) FROM item WHERE price BETWEEN y.a AND y.b) "total"
FROM 
     (SELECT a, b FROM x) AS y

I think it should work. I don't know if is the best way but give it a try?

Assuming you have a table price_range with low and high columns:

SELECT 
    CONCAT(low, '-', high) as range,
    sum(price between low and high) as total
FROM price_range
CROSS JOIN item

If you don't have a table, but have a query instead, do similar to above, but with the query aliased:

SELECT 
    CONCAT(low, '-', high) as range,
    sum(price between low and high) as total
FROM (select low, high from some_table) as price_range
CROSS JOIN item