Union Query with group and Sum (1 Viewer)

[Tskutnik]

Hi all. Hope you are all doing well.

Easy one...

In the Union query below (below the XX's) I need to
Group by:
AsOf,
AcctKey,
SecKey,
PositionRef

and
Sum By:
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,

Thanks

XXXXXXXXXXXXX

SELECT
AsOf,
AcctKey,
SecKey,
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,
PositionRef
FROM Activity_Cash_1;

UNION ALL SELECT
AsOf,
AcctKey,
SecKey,
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,
PositionRef
FROM Activity_Security_1;

 

[theDBguy]

Hi. You could try:

SELECT AsOf, AcctKey, SecKey, PositionRef,
Sum(Quantity_Impact), Sum(MarketVal_Impact), Sum(Cash_Impact)
FROM (Your Union Query Here)
GROUP BY AsOf, AcctKey, SecKey, PositionRef

Hope that helps...

 

[CJ_London]

in the sql window modify your union query to alias it

SELECT * FROM (union query) AS U

then return to the query builder and create your aggregate query

Edit: DBG has provided the detailed solution

 

[Tskutnik]

Thanks all- perfectly simple.... I was a victim of syntax errors.... aren't we all.

 

[theDBguy]

Thanks all- perfectly simple.... I was a victim of syntax errors.... aren't we all.

Hi. Glad to hear you got it sorted out. @CJ_London and I were happy to assist. Good luck with your project.

 

[Tskutnik]

CJ - I did not try your suggestion because I was not clear what you meant (newbie). If that why Access is inserting the "[%$##@_Alias]" text? How exactly do I fix this?


SELECT [%$##@_Alias].AsOf, [%$##@_Alias].AcctKey, [%$##@_Alias].SecKey, [%$##@_Alias].PositionRef, Sum([%$##@_Alias].Quantity_Impact) AS Sum_Quantity_Impact, Sum([%$##@_Alias].MarketVal_Impact) AS Sum_MarketVal_Impact, Sum([%$##@_Alias].Cash_Impact) AS Sum_Cash_Impact
FROM (SELECT
AsOf,
AcctKey,
SecKey,
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,
PositionRef
FROM Activity_Cash_1


UNION ALL SELECT
AsOf,
AcctKey,
SecKey,
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,
PositionRef
FROM Activity_Security_1

) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].AsOf, [%$##@_Alias].AcctKey, [%$##@_Alias].SecKey, [%$##@_Alias].PositionRef;

 

[theDBguy]

CJ - I did not try your suggestion because I was not clear what you meant (newbie). If that why Access is inserting the "[%$##@_Alias]" text? How exactly do I fix this?


SELECT [%$##@_Alias].AsOf, [%$##@_Alias].AcctKey, [%$##@_Alias].SecKey, [%$##@_Alias].PositionRef, Sum([%$##@_Alias].Quantity_Impact) AS Sum_Quantity_Impact, Sum([%$##@_Alias].MarketVal_Impact) AS Sum_MarketVal_Impact, Sum([%$##@_Alias].Cash_Impact) AS Sum_Cash_Impact
FROM (SELECT
AsOf,
AcctKey,
SecKey,
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,
PositionRef
FROM Activity_Cash_1


UNION ALL SELECT
AsOf,
AcctKey,
SecKey,
Quantity_Impact,
MarketVal_Impact,
Cash_Impact,
PositionRef
FROM Activity_Security_1

) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].AsOf, [%$##@_Alias].AcctKey, [%$##@_Alias].SecKey, [%$##@_Alias].PositionRef;

Hi. You can use any alias you like. Try replacing everything Access added for you with something like [MyUnion].

 

[Tskutnik]

Hi. You can use any alias you like. Try replacing everything Access added for you with something like [MyUnion].

All set - worked. Thanks

 

[theDBguy]

All set - worked. Thanks

Hi. Good luck with your project.