I have table products in my database. price column has various repeating values. I want to fetch count of products based on range 10-50, 51-100 and so on.
One way I could identify was to execute multiple queries on the database with different where clauses.
I was wondering if it could be done in one query itself.
what I tried was
$q = "SELECT count(*) FROM products WHERE price>10 AND price<50"
$q1 = "SELECT count(*) FROM products WHERE price>50 AND price<100"
Now this gives me the count of individual ranges, but I have to manually write queries for all the ranges.
select product_name,
count(case when price between 10 and 50 then 1 else null end) as `10-50 count`,
count(case when price between 51 and 100 then 1 else null end) as `51-100 count`
from products
group by product_name