MySql: Count rows in price range?

Question

If I wanted to know how many entries I have where product_price is within the 500-1000 range. How can I do that?

Thanks!

Answer 1

I'm guessing you mean this:

SELECT COUNT(*) FROM products WHERE product_price >= 500 AND product_price <= 1000

But you didn't specify whether your interval is open, closed or half-closed. When using open or closed intervals (the above is a closed interval) you have to be careful not to miss or double count items on the boundary when tabulating all the intervals.

Depending on what you are doing, this might be better for you:

SELECT COUNT(*) FROM products WHERE product_price >= 500 AND product_price < 1000

If you want to get all the intervals, you can do that in one statement too:

SELECT
SUM(CASE WHEN product_price < 500 THEN 1 ELSE 0 END) AS price_range_1,
SUM(CASE WHEN product_price >= 500 AND product_price < 1000 THEN 1 ELSE 0 END) AS price_range_2,
SUM(CASE WHEN product_price >= 1000 THEN 1 ELSE 0 END) AS price_range_3
FROM products

Alternatively (and better in my opinion), store your interval ranges in another table and join with it.

(Many other people have pointed out the BETWEEN keyword. In case you are interested, it's equivalent to the closed interval version, i.e. the first version.)

Answer 2

SELECT COUNT(*) FROM the_table WHERE price BETWEEN 10 AND 20;

Answer 3

Use BETWEEN:

SELECT count(*) FROM table WHERE price BETWEEN 500 AND 1000