1 select * from (
2 select
3 (select isnull(sum(Value),0) from table_Month d where etp.Id = d.Eid and d.Id >= 1701 and d.Id <= 1801 and d.AreaId in (11)) EtpSum,
4 (select isnull(sum(Value),0) from table_Month d where d.Id >= 1701 and d.Id <= 1801 and d.AreaId in (11)) AreaTotal,
5 isnull(hd.CountAsource,0) AsoValue,isnull(py.CountEsource,0) EsoValue,
6 'Group' as GroupCode,etp.Gdp EtpDdp, etp.Id Eid, etp.Name EtpName, etp.ShortName EtpShortName,etp.AreaId
7 from table_Etp etp
8 left join(
9 select count(*) CountAs,Eid from(
10 select count(Id) CountAS,Eid,TargetId from table_AS rpas where rpas.Id >= 170101 and rpas.Id <= 180101 group by Eid,TargetId
11 )a group by Eid
12 )hd on hd.Eid = etp.Id
13 left join(
14 select count(*) CountEs,Eid from(
15 select count(Id) CountES,Eid,TargetId from table_ES rpes where rpes.Id >= 170101 and rpes.Id <= 180101 group by Eid,TargetId
16 )a group by Eid
17 )py on py.Eid = etp.Id
18 )a where a.AreaId in (11)
