Can I combine two columns into one and then count values for them as if it were one column?

I am currently using this SQL statement to return a count by who completed each record (RFI):

SELECT Completed_By, COUNT(DISTINCT Unique_ID) AS RFICount FROM RFI_ 
WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
GROUP BY Completed_By

This returns a data set like the following:

Completed_By     RFICount
------------     ---------
开发者_运维技巧SMITH, Bob       1
DOE, John        15
WARNER, Jack     11

This is how I want it to look. However, someone can be the primary or secondary person who completed or assisted in the completion of a record (RFI). I want to show this all in one column, regardless of whether they completed the record (Completed_By) or assisted with it (Assisting_Analyst).

When I use the following SQL statement, it shows these as separate when they need to be aggregated:

SELECT Completed_By, Assisting_Analyst, COUNT(DISTINCT Unique_ID) AS RFICount 
FROM RFI_ WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
GROUP BY Completed_By, Assisting_Analyst

This returns the following type of output:

Completed_By     Assisting_Analyst     RFICount
------------     -----------------     --------
SMITH, Bob       NULL                  1
DOE, John        NULL                  15
WARNER, Jack     NULL                  7
WARNER, Jack     SMITH, Bob            4

Since Bob Smith assisted Jack Warner with four records (RFIs), there is a separate row created. But I simply want each to get individual credit and have it show up in one column, like so:

Completed_By     RFICount
------------     ---------
SMITH, Bob       5
DOE, John        15
WARNER, Jack     11

In the above example, the four records they worked on jointly are credited to both of them. Granted, this means some records are being counted twice, but that is what I want for this application.

I have tried UNIONS and a bunch of other stuff but it either doesn't give me what I want as I want it displayed or it gives me an error with no data. I know DISTINCT has got to go, but not sure how to do this exactly. This is being done in SQL Server 2008 R2. Help?

---

SELECT t.Completed_By, COUNT(DISTINCT t.Unique_ID) AS RFICount 
    FROM (SELECT Completed_By, Unique_ID
              FROM RFI_ 
              WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
          UNION ALL
          SELECT Assisting_Analyst AS Completed_By, Unique_ID
              FROM RFI_ 
              WHERE Date_Submitted BETWEEN '20110101' AND '20110630'
                  AND Assisting_Analyst IS NOT NULL) t
    GROUP BY t.Completed_By

---

The best way of doing this is probably with a rollup, which avoids the sub-query and would probably perform better on a large datasets.

SELECT Completed_By, Assisting_Analyst, COUNT(DISTINCT Unique_ID) AS RFICount 
FROM RFI_ WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
AND Assisting_Analyst IS NULL and Completed_By IS NOT NULL
GROUP BY Completed_By, Assisting_Analyst WITH ROLLUP

The ROLLUP means that the total value should be included in the Completed_By line, hence the exclusion of the Assisting line in the where clause.

---

SELECT a.Completed_By
     , COUNT(DISTINCT a.Unique_ID)
       + ( SELECT COUNT(DISTINCT b.Unique_ID)
           FROM RFI_ b
           WHERE b.Date_Submitted BETWEEN '20110101' AND '20110630' 
             AND b.Assisting_Analyst = a.Completed_By
         )
       AS RFICount
FROM RFI_  a
WHERE a.Date_Submitted BETWEEN '20110101' AND '20110630' 
GROUP BY a.Completed_By

Another try:

SELECT COALESCE(first.Completed_By, second.Assisting_Analyst)
       AS Completed_By
     , COALESCE(first.RFICount,0) + COALESCE(second.RFICount,0)
       AS RFICount
FROM
  ( SELECT Completed_By
         , COUNT(DISTINCT Unique_ID) AS RFICount
    FROM RFI_ 
    WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
    GROUP BY Completed_By
  ) AS first
FULL JOIN
  ( SELECT Assisting_Analyst
         , COUNT(DISTINCT Unique_ID) AS RFICount
    FROM RFI_ 
    WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
    GROUP BY Assisting_Analyst
  ) AS second
ON first.Completed_By = second.Assisting_Analyst