Items getting double-counted in SQL Server, dependent counting logic not working right
SELECT t.Submitting_Agency, COUNT(DISTINCT t.Count) AS RFICount
FROM (
    SELECT RFI_.Submitting_Agency, RFI_.Unique_ID, _LinkEnd.EntityType_ID1, _LinkEnd.Link_ID as Count
        FROM RFI_
            JOIN _LinkEnd ON RFI_.Unique_ID=_LinkEnd.Entity_ID1
        WHERE _LinkEnd.Link_ID LIKE 'CAS%' AND RFI_.Date_Submitted BETWEEN '20110430' AND '20110630'
    UNION ALL
    SELECT RFI_.Task_Force__Initiative AS Submitting_Agency, RFI_.Unique_ID, _LinkEnd.EntityType_ID1, _LinkEnd.Link_ID as Count
        FROM RFI_
        JOIN _LinkEnd ON RFI_.Unique_ID=_LinkEnd.Entity_ID1
        WHERE _LinkEnd.Link_ID LIKE 'CAS%' AND RFI_.Date_Submitted BETWEEN '20110430' AND '20110630' AND RFI_.Task_Force__Initiative IS NOT NULL) t
GROUP BY t.Submitting_Agency

---------------------------------------------------------------------------
| Unique_ID | Submitting_Agency | Task_Force__Initiative | Date_Submitted |
---------------------------------------------------------------------------
| 1         | Social Service    | Flood Relief TF        | 2011-05-08     |
---------------------------------------------------------------------------
| 2         | Faith-Based Init. | Homeless Shelter Min.  | 2011-06-08     |
---------------------------------------------------------------------------
| 3         | Psychology Group  |                        | 2011-05-04     |
---------------------------------------------------------------------------
| 4         | Attorneys at Law  |                        | 2011-05-05     |
---------------------------------------------------------------------------
| 5         | Social Service    |                        | 2011-05-10     |
---------------------------------------------------------------------------

Social Service        1
Faith-Based Unit.     0
Psychology Group      1
Attorneys at Law      1
Flood Relief TF       1
Homeless Shelter Min. 1

SELECT t.Submitting_Agency ...

SELECT
    CASE t.[Task_Force__Initiative]
        WHEN NULL THEN -- Or whatever value constitutes "empty"
            t.[Submitting_Agency]
        ELSE
            t.[Task_Force__Initiative]
    END ...

SELECT
    COALESCE(RFI_.[Task_Force__Initiative], RFI_.[Submitting_Agency]),
    COUNT(*)
FROM
    RFI_
    JOIN _LinkEnd
        ON RFI_.[Unique_ID]=_LinkEnd.[Entity_ID1]
WHERE
    _LinkEnd.[Link_ID] LIKE 'CAS%'
    AND RFI_.[Date_Submitted] BETWEEN '20110430' AND '20110630'
GROUP BY
    COALESCE(RFI_.[Task_Force__Initiative], RFI_.[Submitting_Agency])

SELECT COLAESCE(Task_Force__Initiative, Submitting_Agency), COUNT(DISTINCT _LinkEnd.Link_ID) AS RFICount
  FROM RFI_
  JOIN _LinkEnd ON RFI_.Unique_ID=_LinkEnd.Entity_ID1
 WHERE _LinkEnd.Link_ID LIKE 'CAS%' AND RFI_.Date_Submitted BETWEEN '20110430' AND '20110630'
 GROUP BY COLAESCE(Task_Force__Initiative, Submitting_Agency);