How to get percentage after grouping?

Question

I have a table like this:

person_id food   data
1         bread   xxx
1         bread   xxx
1         fruite  xxx
2         bread   xxx
2         fruite xxx

food can have only three values (bread, fruits), I want to get the percentage of bread each person has eaten, and there percentage of fruits each person has eaten.

I tried to do:

select count(*) from table
group by person_id, food

and that gives me:

player_id, food, number

but how can I continue from now?

I am on hadoop

Answer 1

select person, t1.food, COUNT() total, COUNT() 100 / fcnt "Perc %" from food_table t1, (select food, COUNT() fcnt from food_table group by food) testsum where t1.food = testsum.food group by t1.person, t1.food, fcnt order by 1

Output person food total Perc % of total 1 bread 2 66 1 fruit 1 50 2 bread 1 33 2 fruit 1 50