Reputation: 41
I have two queries in a linked database (linking tables from two different project databases). I need to combine the queries to get a total count that is grouped by Interaction_Type1. The SQL code is as follows:
Query#1:
SELECT Sum(Temp.cnt) AS SumOfcnt, Temp.Interaction_Type1
FROM (SELECT COUNT(*) as cnt, Interaction_Type1 from AMERILOCKMasterConversionTable
GROUP BY Interaction_Type1
UNION ALL
SELECT COUNT(*), Interaction_Type2 from AMERILOCKMasterConversionTable
GROUP BY Interaction_Type2
UNION ALL
SELECT COUNT(*), Interaction_Type3 from AMERILOCKMasterConversionTable
GROUP BY Interaction_Type3
) AS Temp
GROUP BY Temp.Interaction_Type1;
and Query#2
SELECT Sum(Temp.cnt) AS SumOfcnt, Temp.Interaction_Type1
FROM (SELECT COUNT(*) as cnt, Interaction_Type1 from MARKETMasterConversionTable
GROUP BY Interaction_Type1
UNION ALL
SELECT COUNT(*), Interaction_Type2 from MARKETMasterConversionTable
GROUP BY Interaction_Type2
UNION ALL
SELECT COUNT(*), Interaction_Type3 from MARKETMasterConversionTable
GROUP BY Interaction_Type3
) AS Temp
GROUP BY Temp.Interaction_Type1;
I would like the query to yeild the following results:
Interaction_Type1 Total
Left_Message 23
Made_Contact 16
Bad_Phone_Number 8
No_Answer 12
I am brand new to SQL and have researched all of this online and have had no luck in combining these two queries to produce the desired results.
Any help would be GREATLY appreciated!!
Thanks! Red
Upvotes: 2
Views: 5137
Reputation: 2258
You should have something like
SELECT Integration_Type, SUM(*)
FROM ( SELECT Integration_Type, SumOfcnt FROM VIEW1
UNION ALL
SELECT Integration_Type, SumOfcnt FROM VIEW2)
GROUP BY Integration_Type
but before create views for queries you have provided here, or just gather all your queries in single view.
Upvotes: 1