Postgres - Group types into 'other' category-CodePudding

How could I group by a column, but group specific values into that column into an 'other' category.

Lets say I have a table of sales:

Sales:
id | price | type
1 | 1 | basic
2 | 1 | basic
3 | 2 | premium 
4 | 5 | small
5 | 3 | medium
6 | 2 | large

I'd like to group the following table by the type, but also group small, medium and large sales together into 1 bucket called 'other'.

The final result would look like so:

sum | type
2 | basic
2 | premium
10 | other

This works to get each type individually, but not with the subsequent grouping

select sum(price), type
from sales
group by type

CodePudding user response：

You can use a CASE statement to replace type by 'other' when it is 'small', 'medium' or 'large', then group by using this CASE statement:

SELECT CASE WHEN type IN ('small', 'medium', 'large') THEN 'other' ELSE type END AS type,
       SUM(price)
FROM sales
GROUP BY 1;

Another option is to use a CASE statement into a subquery to replace type by 'other' when it is 'small', 'medium' or 'large'; then group by in the outer subquery:

SELECT type, 
       sum(price)
FROM (SELECT CASE WHEN type IN ('small', 'medium', 'large') THEN 'other' ELSE type END AS type,
             price
      FROM sales) AS sq
GROUP BY type;

CodePudding user response：

Try this :

select sum(price), type
  from sales
 where not type in ('small', 'medium', 'large')
 group by type
union all
select sum(price), 'other' as type
  from sales
 where type in ('small', 'medium', 'large')