select a.*,rtrim(iif(isnull(a.Sum_extload),0,round(a.Sum_ExtLoad/b.Sum_ExtLoad*100,2))) +'%' AS LoadPercengage from (select date,[assembly number],[product name],[commodity],sum ([Ext Load]) as Sum_Extload from FPS_Costbom group by date,[assembly number],[product name],[commodity]
union all
select date,[Assembly Number],[product name],'Total',sum([Ext load]) as Sum_ExtLoad from
FPS_COSTBOM group by date,[Assembly Number],[product name]) a inner join (select date,[assembly number],[product name],sum ([Ext Load]) as Sum_Extload from FPS_Costbom group by date,[assembly number],[product name])b ON (a.[Assembly Number]=b.[Assembly Number]) AND (a.date=b.date);
修改后为:
SELECT a.*, rtrim(IIf(isnull(a.Sum_extload),0,round(a.Sum_ExtLoad/b.Sum_ExtLoad*100,2)))+'%' AS LoadPercengage FROM [select date,[assembly number],[product name],[commodity],sum ([Ext Load]) as Sum_Extload from FPS_Costbom group by date,[assembly number],[product name],[commodity] UNION ALL select date,[Assembly Number],[product name],'Total',sum([Ext load]) as Sum_E] AS a INNER JOIN [select date,[assembly number],[product name],sum ([Ext Load]) as Sum_Extload from FPS_Costbom group by date,[assembly number],[product name]] AS b ON (a.date=b.date) AND (a.[Assembly Number]=b.[Assembly Number]);