--21��ת����ռ��
---����
select 
a.smonth as MONTH_ID,
'����' as ZB_TYPE,
'�ӱ�' as PROV_NAME,
dcg.city_code*100 as CITY_ID,
dcg.city_name as CITY_NAME,
dcg.grid_code as GRID_ID,
dcg.grid_name as GRID_NAME,
'21' as ZB_CODE,
'ת����ռ��' as ZB_NAME,
'��' as IS_RATIO,
a.zgd_num as QZ_FZ,
a.total_num as QZ_FM,
a.zgd_rate as BY_QZ
from
(select yn.smonth,dgc.grid_code,
count(*) as total_num,
sum(case when yn.meter_type_name='12-01 | ת���ֳ���' then 1 else 0 end) as zgd_num,
sum(case when yn.meter_type_name='12-01 | ת���ֳ���' then 1 else 0 end)/count(*)::numeric as zgd_rate
from sqmdb_cost.ytmx_new yn
join sqmdb_cost.dict_grid_code dgc 
on city_name_short(yn.city_name)=dgc.city_name and trim(yn.area_name)=trim(dgc.grid_name)
where yn.build_type_name not in ('�칫¥','�칫�÷�','Ӫҵ�÷�') and yn.smonth='202206'
group by yn.smonth,dgc.grid_code) a
join sqmdb_cost.dict_code_grid dcg on a.grid_code=dcg.grid_code
order by dcg.city_code