1

I've studied SQL many years ago but now my knowledge is very rusty. I will not post any code because my attempts didn't get even close. I am using MySQL.

if a have a table with products

product_id price
1           50
2           70
3           100
4           120
5           900
6           1100

I want to generate links like this on a web page

less than $70           //so that it narrows the result to ABOUT 30%
between $70 and $120    //so that it narrows the result to ABOUT 30%
more than $120          //so that it narrows the result to ABOUT 30%

I think I need a query that returns a minimum value a medium value

Using the example table, if the query returns a minimum value (70), 30% of the products will be bellow this value. A medium value, 30% of the products will be between the minimum value (70) and the medium value (120). Also 30% of the products will be above the medium value value (120).

This is roughtly my idea. I don't want a working code. I just want some example query that helps me to give me a "start".

Diego Alves
  • 2,462
  • 3
  • 32
  • 65

2 Answers2

0

You can solve this by

  • Selecting the "middle" priced products and
  • finding the MIN and MAX price

Use:

Select MIN(middleProducts.price) AS price30Percent, MAX(middleProducts.price) AS price70Percent
from
(
    SELECT products.*, @counter := @counter +1 AS counter
    FROM (select @counter:=0) initvar, products
    ORDER BY price
) middleProducts
where (30/100 * @counter) < counter AND counter <= (70/100 * @counter)

Use price30Percent & price70Percent to generate the links.

References

Jannes Botis
  • 11,154
  • 3
  • 21
  • 39
-1

You can start by using a "case" in the query, it should look like the following:

select `product_id`, `price`, case when price < 70 then 'less than $70'
                                when price between 70 and 120 then 'between $70 and $120'
                                when price >120 then 'more than $120' end as `Price category`
from `table`;

This way you will get a set of data showing the price category of every product, then you can arrange them the way you want.

Abd Alrahman
  • 63
  • 7