Forum Discussion
Help with a Union Query
Hello,
I am pretty new to Union Queries and am looking for some help. I have 2 pretty simple queries that bring back the Count of Distinct SSNs from 2 different places by plan #. The goal of my union query is to combine the counts, so for instance if plan 1 has 3 SSNS in query 1 and 2 SSNS in query 2, the union query would show a Count 5 SSNS. Can this be done? This is my SQL:
SELECT Subquery_AdvDetailPPNTSContrib1.[Plan No], Count(Subquery_AdvDetailPPNTSContrib1.[Soc Sec #]) AS [CountOfSoc Sec #]
FROM Subquery_AdvDetailPPNTSContrib1
GROUP BY Subquery_AdvDetailPPNTSContrib1.[Plan No]
UNION
SELECT Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No], Count(Subquery_AdvDetailPPNTSContribFlatDollar1.[Soc Sec #]) AS [CountOfSoc Sec #]
FROM Subquery_AdvDetailPPNTSContribFlatDollar1
GROUP BY Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No]
The two column names in my two queries are the same: Plan No and Soc Sec #.
The output is giving me two rows for plans with SSNS on both queries- is there a way to add them together to give only 1 row per plan?
- can you first Create a Union query without Totaling:
SELECT Subquery_AdvDetailPPNTSContrib1.[Plan No], Subquery_AdvDetailPPNTSContrib1.[Soc Sec #]
FROM Subquery_AdvDetailPPNTSContrib1
UNION ALL
SELECT Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No], Subquery_AdvDetailPPNTSContribFlatDollar1.[Soc Sec #]
FROM Subquery_AdvDetailPPNTSContribFlatDollar1
Then create your Total Query from the above query.
- arnel_gpSteel Contributorcan you first Create a Union query without Totaling:
SELECT Subquery_AdvDetailPPNTSContrib1.[Plan No], Subquery_AdvDetailPPNTSContrib1.[Soc Sec #]
FROM Subquery_AdvDetailPPNTSContrib1
UNION ALL
SELECT Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No], Subquery_AdvDetailPPNTSContribFlatDollar1.[Soc Sec #]
FROM Subquery_AdvDetailPPNTSContribFlatDollar1
Then create your Total Query from the above query.