How to use reference to another table in aggregate function

Question

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?

Answer 1

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.