
CREATE VIEW ervtp20 AS
SELECT
    `b`.`branch_code` AS `branch_code`,
    `b`.`region` AS `region`,
    COUNT(`ag`.`sr_no`) AS `ervcount`
FROM
    (
        `satgurucrm_shailesh`.`mst_branch` `b`
    LEFT JOIN `satgurucrm_shailesh`.`vw_account_grid` `ag`
    ON
        (
            (
                (`b`.`region` = `ag`.`region_id`) AND(`b`.`branch_code` = `ag`.`branch_code`)
            )
        )
    )
WHERE
    (
        (`ag`.`account_type` = 'ERV') AND(`ag`.`lead_type` = 'ERV-Top 20%')
    )
GROUP BY
    `b`.`branch_code`,
    `b`.`region`