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?
The OUTER APPLY is probably not too bad, but if you did want to replace it with something else here is another option:

With cte As (
    Select GroupId,
        StatusOfRequest,
        HasCertificate = iif(Exists (Select 1 
                                       From FK.Certificate As c
                                       Where c.CertificateRequestID = cr.ID), 1, 0)
      From FK.CertificateRequest As cr)
Select GroupID,
    OnCreateCount = SUM(CASE WHEN StatusOfRequest = 1 AND HasCertificate = 0 THEN 1 END),
    ...
    NewCount = SUM(CASE WHEN StatusOfRequest = 1 AND HasCertificate = 1 THEN 1 END),
    ...
  From cte
  Group By GroupID;


This assumes you have a version of SQL Server which supports iif and common table expressions. If not, easy to translate to CASE and subquery respectively.