with ratio as(
select
    a.area_name as NAME,
    h.consumption_exp as 消費支出,
    h.food_exp as 食糧費,
    h.survey_year as 調査年,
    round(cast(h.food_exp as REAL) / cast(h.consumption_exp as REAL)*100, 1) || "%" as 平均
from
    household_survey as h
inner join
    area as a
on
    a.area_code = h.area_code
)

select _, NAME,調査年,平均 from ratio
where 調査年 = 2012
order by 平均 desc, 調査年