SQL statement for counting the number of items in the MySQL price range

In Excel, we can use this formula to calculate the price segment of each price, and then use the pivot table to get the data:

= LOOKUP (H2, {0, 49.99, 99.99, 149.99, 199.99, 249.99; "050 & Prime;," 100 & Prime;, "150 & Prime ;, "200 & Prime;," 250 & Prime;, "250 + "})

In MySQL, you can use the following command, one step in place:

SELECT price_range, count (*) AS number
FROM
(Select case when Price> = 0 and Price <= 49.99 then '050'
When Price> = 50 and Price <= 99.99 then '000000'
When Price> = 100 and Price <= 149.99 then '000000'
When Price> = 150 and Price <= 199.99 then '000000'
When Price> = 200 and Price <= 249.99 then '000000'
Else '100 +'
End as price_range
FROM products
WHERE 1
) AS price_summaries
Group by price_range
Order by price_range;
In order to facilitate sorting, we have simplified the naming of the numbers, which is equivalent to [50,100), [100,150), [150,200), [200,250), [250 ,)