CREATE VIEW zcy_detail_electricity AS select `a`.`id` AS `id`,`a`.`type` AS `type`,unix_timestamp(concat(`a`.`month`,'-01')) AS `month`,`a`.`dosage` AS `dosage`,round(`a`.`sum`,2) AS `sum` from (select `b`.`id` AS `id`,`b`.`type` AS `type`,date_format(`a`.`start_time`,'%Y-%m') AS `month`,(case when (`b`.`type` = 'B') then sum(`a`.`electricity_consumption`) when (`b`.`type` = 'C') then sum(`a`.`water_consumption`) end) AS `dosage`,(case when (`b`.`type` = 'B') then sum(cast(`a`.`electricity` as char charset binary)) when (`b`.`type` = 'C') then sum(cast(`a`.`water` as char charset binary)) end) AS `sum` from (`zcy`.`zcy_house_billing` `a` join `zcy`.`zcy_bill_electricity` `b` on((`a`.`house_property_id` = `b`.`house_property_id`))) where find_in_set(`a`.`house_number_id`,`b`.`house_number_id`) group by date_format(`a`.`start_time`,'%Y-%m'),`b`.`id`,`b`.`type`,`b`.`house_number_id`) `a`