0

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?

0

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.

  • I have a suspicion that OUTER APPLY may not scale well because it may force a nested loop join, where this example seems to use other joins when the row count increases. – mendosi Nov 2 '16 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.