Group by with diff percentage MySQL

I have been trying to query the increase percentage of a product grouped in certain range depending on the actual increase for ea开发者_Go百科ch run. So for example I have the following table schema

Now, if I query the increase percent I would get something like:

SELECT *, (((newPrice - price)/price) * 100 ) as PERCENTAGE FROM Product

What Im trying to obtain is to group the values within a particular range depending on the calculated increase. So for this run something like this:

| Range   | #COUNT    |
| 0-10%   | 3         | 
| 30-40%  | 1         | 

and more ranges and products if existing

I have tried:

Select *, COUNT(idProduct) AS COUNT FROM Product 
where (((newPrice - price)/price) * 100 ) BETWEEN 0 AND 10

which gives me:

But I need to configure the query in a way it can determine a reasonable percent range (i.e Without hard coding the values BETWEEN 0 AND 10) and group the values in there for different runs. Any ideas?

Thank you!

product table

CREATE  TABLE `product` (    
  `idproduct` INT NOT NULL ,    
  `description` VARCHAR(45) NULL ,    
  `price` INT NULL ,    
  `newPrice` INT NULL ,    
  PRIMARY KEY (`idproduct`) );

You could group by 10% percentage bands:

SELECT  floor((newPrice - price) / price * 10) * 10 as LowerBound
,       floor((newPrice - price) / price * 10) * 10 + 10 as HigherBound
,       count(*) as Count
FROM    Product
GROUP BY
        floor((newPrice - price) / price * 10)

Multiplying by 10 and rounding down should create one group per 10 percentage points.

SELECT
  r.RangeValue,
  COUNT(*) AS CountofProducts
FROM (SELECT 
        CASE WHEN (((newPrice - price)/newPrice) * 100 ) <= 10 THEN
          '0-10%'
        WHEN (((newPrice - price)/newPrice) * 100 ) <= 20 THEN
          '10-20%'
        WHEN (((newPrice - price)/newPrice) * 100 ) <= 30 THEN
          '20-30%'
        WHEN (((newPrice - price)/newPrice) * 100 ) <= 40 THEN
          '30-40%'
        ELSE
          '40+%'
        END AS RangeValue
      FROM Product) r
GROUP BY r.RangeValue

Its simple SELECT count(*) as items, (((newPrice - price)/newPrice) * 100 ) as PERCENTAGE ,

IF ( ((newPrice - price)/newPrice* 100) >=0 &&
((newPrice - price)/newPrice* 100) <=10 , "0 - 10%" , IF ( ((newPrice - price)/newPrice* 100) >=11 &&
((newPrice - price)/newPrice* 100) <=30 , "10 - 30%", so on.... ) ) AS 'range'

FROM Product group by range

Basically, you have to use nested if() statements and inside them you put the ranges.

Then give it a meaningful alias. You can then group by the alias.