GROUP BY - How to create 3 group for the column?-CodePudding

Say I have a table of products, fields are id, number_of_product, price Let's price is min = 100, max = 1000* How to create 3 groups for this column (PostgreSQL) - 100-400, 400-600, 600-1000*

*PS - it would be nice to know how to split into 3 equal parts.

SELECT COUNT(id),
COUNT(number_of_product), 
!!!! price - ?!
FROM Scheme.Table
GROUP BY PRICE

CodePudding user response：

You can try next query:

with p as (
  select 
      *,
      min(price) over() min_price,
      (max(price) over() - min(price) over()) / 3 step
  from products
) select 
    id, product, price,
    case 
        when price < min_price   step then 'low_price'
        when price < min_price   2 * step then 'mid_price'
        else 'high'
    end as category
from p
order by price;

PostgreSQL fiddle

CodePudding user response：

To do this quickly, you can use a case statement to set the groups.

CASE WHEN price BETWEEN 100 AND 400 THEN 1 WHEN price BETWEEN 400 AND 600 THEN 2 WHEN price BETWEEN 600 AND 1000 THEN 3 ELSE 0 END

You would group on this.

For splitting into equal parts, you would use the NTILE window function to group.

NTILE(3) OVER (
    ORDER BY price]
)