CREATE VIEW `hops_full` AS select `hops`.`id` AS `id`,`hops`.`checked` AS `checked`,`hops`.`name` AS `name`,`hops`.`IHGC_code` AS `IHGC_code`,`hops`.`category` AS `category`,`hops`.`origin` AS `origin`,`hops`.`shortdesc` AS `shortdesc`,`hops`.`description` AS `description`,`hops`.`link` AS `link`,(select group_concat(`aromas`.`name` order by `aromas`.`name` ASC separator ', ') from (`aromas` join `aromas_to_hops` on(`aromas_to_hops`.`aromas_id` = `aromas`.`id`)) where `aromas_to_hops`.`hops_id` = `hops`.`id`) AS `aroma`,(select group_concat(`styles`.`name` order by `styles`.`name` ASC separator ', ') from (`styles` join `styles_to_hops` on(`styles_to_hops`.`styles_id` = `styles`.`id`)) where `styles_to_hops`.`hops_id` = `hops`.`id`) AS `use_for`,(select group_concat(`h2`.`name` order by `h2`.`name` ASC separator ', ') from (`hops` `h2` join `hops_to_hops` on(`hops_to_hops`.`replacements_id` = `h2`.`id`)) where `hops_to_hops`.`hops_id` = `hops`.`id`) AS `replacement`,cast((select avg(`hops_analysis`.`alpha`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `alpha`,cast((select avg(`hops_analysis`.`alpha_min`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `alpha_min`,cast((select avg(`hops_analysis`.`alpha_max`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `alpha_max`,cast((select avg(`hops_analysis`.`beta`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `beta`,cast((select avg(`hops_analysis`.`cohumulon`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `cohumulon`,cast((select avg(`hops_analysis`.`colupulon`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `colupulon`,cast((select avg(`hops_analysis`.`polyphenol`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `polyphenol`,cast((select avg(`hops_analysis`.`xanthohumol`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `xanthohumol`,cast((select avg(`hops_analysis`.`oil`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `oil`,cast((select avg(`hops_analysis`.`myrcen`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `myrcen`,cast((select avg(`hops_analysis`.`linalool`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `linalool`,cast((select avg(`hops_analysis`.`caryophyllen`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `caryophyllen`,cast((select avg(`hops_analysis`.`aromadendren`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `aromadendren`,cast((select avg(`hops_analysis`.`humulen`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `humulen`,cast((select avg(`hops_analysis`.`farnesen`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `farnesen`,cast((select avg(`hops_analysis`.`betaselinen`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `betaselinen`,cast((select avg(`hops_analysis`.`alphaselinen`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `alphaselinen`,cast((select avg(`hops_analysis`.`loss`) from `hops_analysis` where `hops_analysis`.`hop` = `hops`.`id`) as decimal(4,1)) AS `loss` from `hops` order by `hops`.`name`