How to use reference to another table in aggregate function

I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write: SELECT [GroupId] = cr.[GroupId], [OnCreateCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 THEN 1 ELSE 0 END), [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END), [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND THEN 1 ELSE 0 END), [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END), [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END), [TotalCount] = COUNT(*) FROM [FK].[CertificateRequest] cr GROUP BY cr.[GroupId] Here OnCreateCount and CreatedCount are the same, but it's wrong. CreatedCount is number of rows with status 1 which are referenced from table [Certificate]. OnCreate is number of rows with status 1 which are **not** referenced from table [Certificate] Here is how I solved it: SELECT [GroupId] = cr.[GroupId], [OnCreateCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NULL THEN 1 ELSE 0 END), [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END), [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NOT NULL THEN 1 ELSE 0 END), [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END), [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END), [TotalCount] = COUNT(*) FROM [FK].[CertificateRequest] cr OUTER APPLY ( SELECT TOP 1 * FROM [FK].[Certificate] c WHERE c.CertificateRequestID = cr.ID ) c GROUP BY cr.[GroupId] But I really don't like OUTER APPLY here which is used just to determine whenever there is any row in referenced table. What is the easiest way to get required result?