CODEWITHSUNDEEP
mysqlsql
Bartzilla
Bartzilla

Reputation: 2794

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 each run. So for example I have the following table schema

product table

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

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

products with their respective percentage

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:

enter image description here

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`) );

Upvotes: 3

Views: 980

Answers (3)

Andomar
Andomar

Reputation: 238106

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.

Upvotes: 9

John Hartsock
John Hartsock

Reputation: 86882

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

Upvotes: 1

Imdad
Imdad

Reputation: 6032

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.

Upvotes: 0

Related Questions