CREATE VIEW `hops_short` AS select `hops`.`id` AS `id`,`hops`.`name` AS `name`,`hops`.`category` AS `category`,cast((select avg(`hops_analysis`.`alpha`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id` group by `hops_analysis`.`hop`) as decimal(4,1)) AS `alpha`,cast((select avg(`hops_analysis`.`oil`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id` group by `hops_analysis`.`hop`) as decimal(4,1)) AS `oil`,`hops`.`shortdesc` AS `shortdesc` from `hops` where `hops`.`checked` = 1 order by `hops`.`name`