Regrouping data to decrease size of dataset

[mchoss]

Is there a simple way using sql to achieve the following, turn:

potatoes 10
carrots 7
beans 3
beef 5
lamb 4
pork 1

into:

vegetables 20
meat 10

[simple example]

The decode function seems limited to single values, and the group by function is not really appropriate as which items fall under which group need to be defined.

I have considered creating another column and then populating this column with the classifications, is this the only/best way?

Thanks in advance

 

[swampBoogie]

Oracle do support case expressions.

select category,count(*)
from (select 
case when c in ('Potatoes','Carrots','beans')
  then 'Vegetables'
  else 'Meat' end as category
  from t) dt
group by category

change c and t for your column and table.