title: How to remove duplicating rows from union statement
body: <p>OK - I have looked and looked and found a lot of examples but nothing quite meeting my need.  Maybe I used the wrong words to search with, but I could use your help.  I will provide as much detail as I can.</p>
  
  <p>I need to produce a report that merges fields from two tables, or rather a view and a table, into one table for a report.  Here is the statement I am using:</p>
  
  <pre><code>SELECT A.ConfInt, A.Conference, 
          NULL as Ordered,
          NULL as Approved,
          NULL as PickedUp,
          SUM(dbo.Case_Visit_Payments.Qty) AS Qty
  FROM         dbo.Conferences as A INNER JOIN
                        dbo.Case_Table ON A.ConfInt = dbo.Case_Table.Conference_ID INNER JOIN
                        dbo.Case_Visit_Payments ON dbo.Case_Table.Case_ID = dbo.Case_Visit_Payments.Case_ID
  WHERE     (dbo.Case_Visit_Payments.Item_ID = 15 AND A.ProjectCool = 1)
  GROUP BY A.Conference, A.ConfInt
  UNION
  SELECT  B.ConfInt, 
          B.Conference, 
          SUM(dbo.Cool_Fan_Order.NumberOfFansRequested) AS Ordered, 
          SUM(dbo.Cool_Fan_Order.Qty_Fans_Approved) AS Approved, 
          SUM(dbo.Cool_Fan_Order.Qty_Fans_PickedUp) AS PickedUp, 
          NULL AS Qty
  FROM         dbo.Conferences as B LEFT OUTER JOIN
                        dbo.Cool_Fan_Order ON B.ConfInt = dbo.Cool_Fan_Order.Conference_ID
  where B.ProjectCool = 1
  GROUP BY B.Conference, B.ConfInt
  </code></pre>
  
  <p>And here are the results:</p>
  
  <pre><code>4   Our Lady        NULL    NULL    NULL    11
  4   Our Lady        40      40      40      NULL
  7   Holy Rosary     20      20      20      NULL
  11  Little Flower   NULL    NULL    NULL    21
  11  Little Flower   5       5       20      NULL
  19  Perpetual Help  NULL    NULL    NULL    2
  19  Perpetual Help  20      20      20      NULL
  </code></pre>
  
  <p>What I would strongly prefer is to not have the duplicating rows, such as:</p>
  
  <pre><code>4   Our Lady        40      40      40      11
  7   Holy Rosary     20      20      20      NULL
  11  Little Flower   5       5       20      21
  19  Perpetual Help  20      20      20      2
  </code></pre>
  
  <p>I hope this question was clear enough.  Any Suggestions would be greatly appreciated.  And I do mark as answered. :)</p>
  
  <p>Gregory</p>