How can I get max 20 group by count between min and max value-CodePudding

I have a table call products. Example in this products table, there has 2 thousands iPhone 11 with difference prices. Here Minimum price is example 400 USD and Max price is 30000 USD

From this range I need total 20 range group by count products. Example

400 - 1520 = 10
1520 - 3040 = 20 
-----
-----
28480 - 30000 = 23

I am just able to do a range query to find between products

SELECT * FROM products where price BETWEEN 400 AND 300000

Here how can get 20 range group by count between two value ?

CodePudding user response：

You can use the NTILE() function to divide the data into specific number of groups.

SELECT
bucket_no,
min(price),
max(price),
count(*)
FROM
(SELECT
price,
ntile(20)Over(order by price asc) as bucket_no
from table)A
group by bucket_no;

If you want to do this for every category of product, then you can use partition by in the NTILE() function.

Refer this for further understanding: https://www.mysqltutorial.org/mysql-window-functions/mysql-ntile-function/