Combining Results in a Union Query?

[supportservice]

Hello,
I have this UNION query but need to have the totals summed when the MainAccountDesc is the same.

 SELECT RotocoSummary.AccountCategoryDesc, RotocoSummary.MainAccountDesc, RotocoSummary.MTD, RotocoSummary.LMTD, RotocoSummary.YTD, RotocoSummary.LYTD
FROM RotocoSummary LEFT JOIN SB1Summary ON (RotocoSummary.MainAccountDesc = SB1Summary.MainAccountDesc) AND (RotocoSummary.AccountCategoryDesc = SB1Summary.AccountCategoryDesc)

UNION SELECT SB1Summary.AccountCategoryDesc, SB1Summary.MainAccountDesc, SB1Summary.MTD, SB1Summary.LMTD, SB1Summary.YTD, SB1Summary.LYTD
FROM RotocoSummary LEFT JOIN SB1Summary ON (RotocoSummary.MainAccountDesc = SB1Summary.MainAccountDesc) AND (RotocoSummary.AccountCategoryDesc = SB1Summary.AccountCategoryDesc);

RESULT:

 AccountCategoryDesc	MainAccountDesc	MTD	LMTD	YTD	LYTD
Assets	ACCUMULATED DEPRECIATION	0	3316.17	10149.61	13264.68
Assets	ACCUMULATED DEPRECIATION	0	21615.27	23158	86461.08

It should be combined not two separate lines.
I'm not sure how to do that?

 

[PHV]

Perhaps this ?

SELECT AccountCategoryDesc,MainAccountDesc,Sum(MTD) AS totMTD,Sum(LMTD) AS totLMTD,Sum(YTD) AS totYTD,Sum(LYTD) AS sLYTD
FROM (SELECT AccountCategoryDesc,MainAccountDesc,MTD,LMTD,YTD,LYTD FROM RotocoSummary
UNION ALL
SELECT AccountCategoryDesc,MainAccountDesc,MTD,LMTD,YTD,LYTD FROM SB1Summary) U
GROUP BY AccountCategoryDesc,MainAccountDesc

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886