What is wrong with my UNION?

This code
SELECT
    , e.cfFull_Name
    , OP.Total_Count tc
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                               ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc               ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob                      ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company	co			  ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em	              ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
     , e.cfFull_Name
    , OP.Total_Count

Open in new window


Returns this:

cfFullName                       tc                  rintd

Name_1                             15                     6
Name_2                             21                     11
Name_3                             17                     18
Name_4                             30                     1
Name_5                             19                     45
Name_6                             32                     9

Which works as it should.

However,  when I perform a Union, no duplicates, a problem develops, here is the UNION:

SELECT
      e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                       ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc       ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob              ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company	co	  ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
    E.cfFull_Name
    , OP.Total_Count

 
 UNION
 
 
 SELECT  
     e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                       ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc       ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob              ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company	co        ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Call_ em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
    GROUP BY
    E.cfFull_Name
    , OP.total_count

Open in new window


Returns this:
cfFullName                       tc                  rintd

Name_1                             15                     6
Name_2                             21                     11
Name_3                             17                     18
Name_3                             17                     1
Name_4                             30                     1
Name_5                             19                     45
Name_5                             19                      1
Name_6                             32                     9

Note the duplicate names at Name_3 & Name_5 & duplicate tc and just a one rintd.
The only change made between the two queries is line 24 on the first query and Line 65 on the second query. My expectations would be no duplicate FullName, no duplicate tc, and the rintd count column would be added, like on Name_5 45 +1 would be 46.

What went wrong?
rhservanAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
union will not do the sum, instead you have to write a sum on the whole result set as below

;with CTE
AS
(
SELECT
      e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                       ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc       ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob              ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company	co	  ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
    E.cfFull_Name
    , OP.Total_Count

 
 UNION
 
 
 SELECT  
     e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                       ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc       ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob              ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company	co        ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Call_ em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
    GROUP BY
    E.cfFull_Name
    , OP.total_count
)
select cfFull_Name,tc,sum(rintd) as rintd
from CTE
group by cfFull_Name,tc

Open in new window

0
 
rhservanAuthor Commented:
Thanks Neo - Tested & verified.
0
 
PortletPaulfreelancerCommented:
Be careful, you could be getting the wrong results.

Please try exactly the same query but make this one change to:

UNION ALL

============
also:

Returns this:
cfFullName                       tc                  rintd

Name_1                             15                     6
Name_2                             21                     11
Name_3                             17                     18 --<< from Query 1
Name_3                             17                     1   -- << from Query 2

Name_4                             30                     1
Name_5                             19                     45
Name_5                             19                      1
Name_6                             32                     9

UNION does make rows unique - but the WHOLE row is evaluated, so those 2 entries for Name_3 are because 18 is different to 1.

But what would happen if both query1 and query2 gave 18 in that third column?
One of those rows would disappear

& if one of those rows disappears the sum would be 18
should the sum be 36?


Please try
UNION ALL

you are at risk of producing the wrong results - just double check
0
 
PortletPaulfreelancerCommented:
and, what is achieved by this singular difference?

INNER JOIN Snival..Email em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
INNER JOIN Snival..Call_ em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id

Open in new window


in fact it appears that neither of those tables are referenced in any other place than those joins (not in the where clause, not in the selection clause)

looks to me like you could remove those tables altogether and the results would be the same.

sorry but I suspect your query needs some re-thinking
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.