MySql: the number of rows in the price range?

Question

If I wanted to know how many records I have, where product_price is in the range of 500-1000. How can i do this?

Thanks!

Answer 1

I assume you mean this:

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

But you did not indicate whether your interval is open, closed or closed. When using open or closed intervals (the above is a closed interval), you must be careful not to miss or double the number of points on the border when tabulating all 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 this in one statement:

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

(, , ), .

( BETWEEN. , , , .)

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