CODEWITHSUNDEEP
sqlms-access
user1860787
user1860787

Reputation: 41

SUM two SELECT COUNT Queries with GROUP BY

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

Answers (1)

paramosh
paramosh

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

Related Questions