Group by Union Query

[Tskutnik]

Quick one - I'm a newbie. The following Group by logic is not working. What am I missing?.
Thanks for the help. I'm sure it is a semi-colon or something in the wrong place.

Code:

SELECT
Sec_Key,
Sec_SourceKey
FROM SecurityMaster_Source

UNION ALL Select
Sec_Key,
Sec_SourceKey
FROM SecurityMaster_Enhanced

GROUP BY [Sec_Key], [Sec_SourceKey];

Result....

The output is not grouping. There 2 matching rows in the underling 2 tables and they are both showing (duplicated and not grouped) in the output.

 

[pbaldy]

The GROUP BY clause is only be applied to the second SELECT statement; each stands alone. Have you tried UNION instead of UNION ALL? UNION alone eliminates duplicates.

 

[plog]

A UNION should query should just bring data together. No grouping, no criteria, no calculations:

SELECT * FROM A
UNION SELECT * FROM B

When you need to GROUP, or apply criteria or make calculated fields you do that in another query based on the UNION:

SELECT Field1, SUM(Field2) AS Total
FROM UnionQuery
GROUP BY Field1

 

[pbaldy]

All true, but in this case just dropping ALL should return the desired result.

 

[Tskutnik]

Got it - both responses were helpful, thanks

 

[pbaldy]

No problem.