Aggregation Errors

[memkam]

I have 2 queries and both run fine seperately, but when I combine them to get one file, it just messes up the numbers.

The following is the combined query:

SELECT Sum(E.[Total Amount]) AS [Total Amount],
sum(IIf(E.Code<>7777,(E.[Total Amount]))) AS [Total Amount To A],
sum(IIf(E.Code=7777,(E.[Total Amount]))) AS [Total Amount To O],

SUM(X.[Actual Cost]-X.[Negotiated Prices]) AS [Total Discrepancy],
SUM(IIf(X.Code<>7777,(X.[Actual Cost]-X.[Negotiated Prices]))) AS [Total Discrepancy To A],
SUM(IIf(X.AgencyCode=7777,(X.[ActualCost]-X.[Negotiated Prices]))) AS [Total Discrepancy To O]

FROM E, X;

Thanks for reading!

 

[cmmrfrds]

This might work.

SELECT Sum(E.[Total Amount]) AS [Total Amount],
sum(IIf(E.Code<>7777,(E.[Total Amount]))) AS [Total Amount To A],
sum(IIf(E.Code=7777,(E.[Total Amount]))) AS [Total Amount To O],
sum(0) AS [Total Discrepancy],
sum(0) AS [Total Discrepancy To O]
FROM E
UNION ALL
Select
sum(0),
sum(0),
SUM(X.[Actual Cost]-X.[Negotiated Prices]) AS [Total Discrepancy],
SUM(IIf(X.Code<>7777,(X.[Actual Cost]-X.[Negotiated Prices]))) AS [Total Discrepancy To A],
SUM(IIf(X.AgencyCode=7777,(X.[ActualCost]-X.[Negotiated Prices]))) AS [Total Discrepancy To O]
FROM X;

 

[memkam]

I guess its a work around - but this was just a sample, I am dealing with approximatley 30 calculations. It will be harsh!

Thanks for the idea - M

 

[PHV]

Assuming you want to combine queryE and queryX, you may try this third query:
SELECT * FROM queryE, queryX;
You may even try this:
SELECT * FROM
(SELECT Sum(E.[Total Amount]) AS [Total Amount],
sum(IIf(E.Code<>7777,(E.[Total Amount]))) AS [Total Amount To A],
sum(IIf(E.Code=7777,(E.[Total Amount]))) AS [Total Amount To O] FROM E) As A
, (SELECT SUM(X.[Actual Cost]-X.[Negotiated Prices]) AS [Total Discrepancy],
SUM(IIf(X.Code<>7777,(X.[Actual Cost]-X.[Negotiated Prices]))) AS [Total Discrepancy To A],
SUM(IIf(X.AgencyCode=7777,(X.[ActualCost]-X.[Negotiated Prices]))) AS [Total Discrepancy To O]) As B
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244