select b.MKT_AREA_NAME, b.MKT_CHANNEL_NAME, b.MKT_GRID_NAME, c.product_name, case when d.CUST_GRP_TYPE=10 then '政企客户' when d.CUST_GRP_TYPE=10 then '家庭客户' else '个人客户' end type, case when d.CUST_MARKET_TYPE=10 then '行业市场' when d.CUST_MARKET_TYPE=20 then '聚类市场' when d.CUST_MARKET_TYPE=30 then '公众市场' end type2, sum(a.ONLN_***) as new_cnt, sum(a.OFFLN_***) as remove_cnt, sum(a.SERV_NUM) as serv_cnt from DS_CHN_PRD_SERV_20120817 a left join DIM_ZONE_AREA b on a.MKT_GRID_ID=b.MKT_GRID_ID left join DIM_PRODUCT c on a.PRODUCT_ID=c.PRODUCT_ID left join DS_CHN_PRD_CUST_20120817 d on a.MKT_CUST_ID=d.MKT_CUST_ID group by b.MKT_AREA_NAME,b.mkt_channel_name,b.mkt_grid_name,c.product_name,type,type2