SQL Server 2008 problem

I have table in following structure...

        ReturnID SumbitID Status

          1           1    1 
          1          NULL  2
          2            2   3
          3            3   1
          3            3   1

I want this output.....

        RetunrID TotalAttempt Success
           1        2           1
           2        1           0
           3        2           2

Count Of ReturnID is Tota开发者_Python百科lAttempt, when ReturnID = sumbitID and status =1 Then it count as success... Thanks in Advance!

Something like this

SELECT
    T.ReturnID
,   COUNT(T.ReturnID) AS TotalAttempt
,   SUM(CASE WHEN T.ReturnID = T.sumbitID AND T.Status = 1 THEN 1 ELSE 0 END) AS Status
FROM dbo.MyTable T
group by T.ReturnID

Check this:

SELECT T.ReturnID , COUNT(T.ReturnID) AS TotalAttempt , SUM(CASE WHEN T.ReturnID = T.sumbitID AND T.Statusa = 1 THEN 1 ELSE 0 END) AS Status FROM @table T GROUP BY T.ReturnID