DROP VIEW IF EXISTS `companies_allocations_view`;
CREATE VIEW `companies_allocations_view` AS
select `ca`.`id`            AS `id`,
       `ca`.`company_id`    AS `company_id`,
       `ca`.`allocation_id` AS `allocation_id`,
       `ca`.`amount`        AS `amount`,
       `ca`.`crush_balance` AS `crush_balance`,
       `ca`.`date_created`  AS `date_created`,
       `ca`.`created_by`    AS `created_by`,
       `ca`.`date_modified` AS `date_modified`,
       `ca`.`modified_by`   AS `modified_by`,
       sum(`s`.`weight`)    AS `total_weight_shipped`,
	   sum((case when (`s`.`product` = 'soybean') then `s`.`weight` else 0 end)) AS `soybeans_weight_shipped`
from (`companies_allocations` `ca`
    left join `shipments` `s` on ((`ca`.`id` = `s`.`company_allocation_id`))
)
group by `ca`.`id`;