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
精彩评论