SELECT 
(SDMS_OrganiseUnit.OrganiseUnitName), 
COUNT(FMDS_Water_DikeProject.OrganiseUnitName)as shu,
sum(FMDS_Water_DikeProject.NowDikeLength) as NowDikeLength,
(sum(NowAverageIncrease)/COUNT(FMDS_Water_DikeProject.OrganiseUnitName)) as NowAverageIncrease,
(sum(NowCrestWidth)/COUNT(FMDS_Water_DikeProject.OrganiseUnitName)) as NowCrestWidth,
COUNT(FMDS_Water_NewReservoir.OrganiseUnitName)as GeShu,
sum(SIrrTotal) as SIrrTotal,
  
--(select Count(FMDS_Water_DikeProject_Backwater.Name) from FMDS_Water_DikeProject_Backwater
 --where  FMDS_Water_DikeProject_Backwater.MainID=FMDS_Water_DikeProject.ID and isdelete=0)  as Swsl
sum(t.Swsl) as Swsl,
sum(t1.Bs) as bs,
sum(t2.sz) as sz,
sum(t3.Swgcd) as Swgcd,
sum(t4.Total)/10000 as toatl,
sum(t5.Total1) as toatl1,
sum(ControlArea) as ControlArea,
(sum(FMDS_Water_NewReservoir.CompletedTotal)+ sum(FMDS_Water_NewReservoir.UnfinishedTotal))
as CompletedTotal
 
  
FROM SDMS_OrganiseUnit 
 
left join FMDS_Water_NewReservoir 
on SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_NewReservoir.OrganiseUnitID and FMDS_Water_NewReservoir.IsDelete=0
  
left join FMDS_Water_DikeProject 
on SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_DikeProject.OrganiseUnitID and FMDS_Water_DikeProject.IsDelete=0
  
 
 
 
left join
(
select MainID,Count(FMDS_Water_DikeProject_Backwater.Name) as Swsl
from FMDS_Water_DikeProject_Backwater 
where  isdelete=0
group by FMDS_Water_DikeProject_Backwater.MainID 
)t  
on t.MainID=FMDS_Water_DikeProject.ID 
 
 
left join 
(
  select MainID, Count(FMDS_Water_DikeProject_Buildings.Name) as Bs 
  from FMDS_Water_DikeProject_Buildings  
  where FMDS_Water_DikeProject_Buildings.Type=1 and  isdelete=0
  group by FMDS_Water_DikeProject_Buildings.MainID 
   
)  t1
on t1.MainID = FMDS_Water_DikeProject.ID
 
left join 
(
  select MainID, sum(FMDS_Water_Quantities.TotalALL) as Total 
  from FMDS_Water_Quantities  
  where    isdelete=0
  group by FMDS_Water_Quantities.MainID 
   
)  t4
on t4.MainID = FMDS_Water_DikeProject.ID
 
 
left join 
(
  select MainID, sum(FMDS_Water_Investment.TotalALL) as Total1 
  from FMDS_Water_Investment  
  where    isdelete=0
  group by FMDS_Water_Investment.MainID 
   
)  t5
on t5.MainID = FMDS_Water_DikeProject.ID
 
 
 
 
 
 
 
 
left join 
(
  select MainID, Count(FMDS_Water_DikeProject_Buildings.Name) as Sz 
  from FMDS_Water_DikeProject_Buildings  
  where FMDS_Water_DikeProject_Buildings.Type=2 and  isdelete=0
  group by FMDS_Water_DikeProject_Buildings.MainID 
   
)  t2
on t2.MainID=FMDS_Water_DikeProject.ID 
 
left join 
(
  select MainID, Count(FMDS_Water_DikeProject_Point.Name) as Swgcd 
  from FMDS_Water_DikeProject_Point  
  where  isdelete=0
  group by FMDS_Water_DikeProject_Point.MainID 
   
)  t3
on t3.MainID=FMDS_Water_DikeProject.ID 
 
 
 
 
  
  
WHERE OrganiseUnitModel=1 AND OrganiseUnitType=3 
AND SDMS_OrganiseUnit.IsDelete=0 
group by SDMS_OrganiseUnit.OrganiseUnitName,DisplayIndex
ORDER BY DisplayIndex