select
       distinct (cus.userid)
     , sum(case cus.status when 'P' then 1 else 0 end) as status1count
     , sum(case cus.status when 'C' then 1 else 0 end) as status2count
     , sum(case cus.status when 'A' then 1 else 0 end) as status3count
     , count(cus.status) as fullcount
 from
   myTable cus
 group by
   cus.userid

(1 row(s) affected)

  |--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1010])))
       |--Hash Match(Aggregate, HASH:([cus].[USERID]), RESIDUAL:([cus].[USERID]=[cus].[USERID]) DEFINE:([Expr1001]=SUM(If ([cus].[STATUS]='P') then 1 else 0), [Expr1002]=SUM(If ([cus].[STATUS]='C') then 1 else 0), [Expr1003]=SUM(If ([cus].[STATUS]='A') then 1 else 0), [Expr1010]=COUNT(*)))
            |--Clustered Index Scan(OBJECT:([MYDATABASE].[dbo].[MYTABLE].[MYTABLE_PK] AS [cus]))

Δεύτερη περίπτωση:

SELECT    
   cus.userid
 , cus.status
 , COUNT(cus.userid) AS GroupCount
 , cuscopy.cnt
FROM        
 myTable as cus
INNER JOIN
 (
 SELECT
    cus1.userid
  , count (cus1.userid) as cnt
 FROM
  myTable cus1
 GROUP BY
  cus1.userid) AS cuscopy
ON cus.userid= cuscopy.userid
GROUP BY cus.userid, cus.status, cuscopy.cnt
ORDER BY cus.userid, cus.status

(1 row(s) affected)

  |--Sort(ORDER BY:([cus1].[USERID] ASC, [cus].[STATUS] ASC))
       |--Hash Match(Inner Join, HASH:([cus1].[USERID])=([cus].[USERID]), RESIDUAL:([cus1].[USERID]=[cus].[USERID]))
            |--Compute Scalar(DEFINE:([Expr1001]=Convert([Expr1009])))
            |    |--Hash Match(Aggregate, HASH:([cus1].[USERID]), RESIDUAL:([cus1].[USERID]=[cus1].[USERID]) DEFINE:([Expr1009]=COUNT_BIG([cus1].[USERID])))
            |         |--Clustered Index Scan(OBJECT:([MYDATABASE].[dbo].[MYTABLE].[MYTABLE_PK] AS [cus1]))
            |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1010])))
                 |--Hash Match(Aggregate, HASH:([cus].[USERID], [cus].[STATUS]), RESIDUAL:([cus].[USERID]=[cus].[USERID] AND [cus].[STATUS]=[cus].[STATUS]) DEFINE:([Expr1010]=COUNT(*)))
                      |--Clustered Index Scan(OBJECT:([MYDATABASE].[dbo].[MYTABLE].[MYTABLE_PK] AS [cus]))

Πράγμα που με κάνει γενικά να πιστεύω οτι τα CASE statements λειτουργούν πολύ καλύτερα από τα inner joins σε αυτή την περίπτωση.