Rewrite a query with GROUP BY ALL

Question

Microsoft has deprecated GROUP BY ALL and while the query might work now, I'd like to future-proof this query for future SQL upgrades.

Currently, my query is:

SELECT       qt.QueueName AS [Queue] ,
             COUNT ( qt.QueueName ) AS [#ofUnprocessedEnvelopes] ,
             COUNT (   CASE WHEN dq.AssignedToUserID = 0 THEN 1
                            ELSE NULL
                       END
                   ) AS [#ofUnassignedEnvelopes] ,
             MIN ( dq.DocumentDate ) AS [OldestEnvelope]
FROM         dbo.VehicleReg_Documents_QueueTypes AS [qt]
             LEFT OUTER JOIN dbo.VehicleReg_Documents_Queue AS [dq] ON dq.QueueID = qt.QueueTypeID
WHERE        dq.IsProcessed = 0
             AND dq.PageNumber = 1
GROUP BY ALL qt.QueueName
ORDER BY     qt.QueueName ASC;

And the resulting data set:

<table><tbody><tr><td>Queue</td><td>#ofUnprocessedEnvelopes</td><td>#ofUnassignedEnvelopes</td><td>OldestEnvelope</td></tr><tr><td>Cancellations</td><td>0</td><td>0</td><td>NULL</td></tr><tr><td>Dealer</td><td>26</td><td>17</td><td>2018-04-06</td></tr><tr><td>Matched to Registration</td><td>93</td><td>82</td><td>2018-04-04</td></tr><tr><td>New Registration</td><td>166</td><td>140</td><td>2018-03-21</td></tr><tr><td>Remaining Documents</td><td>2</td><td>2</td><td>2018-04-04</td></tr><tr><td>Renewals</td><td>217</td><td>0</td><td>2018-04-03</td></tr><tr><td>Transfers</td><td>296</td><td>245</td><td>2018-03-30</td></tr><tr><td>Writebacks</td><td>53</td><td>46</td><td>2018-04-09</td></tr></tbody></table>

I've tried various versions using CTE's and UNION's but I cannot get result set to generate correctly - the records that have no counts will not display or I will have duplicate records displayed.

Any suggestions on how to make this work without the GROUP BY ALL?

Below is one attempt where I tried a CTE with a UNION:

;WITH QueueTypes ( QueueTypeID, QueueName )
AS ( SELECT QueueTypeID ,
            QueueName
     FROM   dbo.VehicleReg_Documents_QueueTypes )
SELECT   qt.QueueName AS [Queue] ,
         COUNT ( qt.QueueName ) AS [#ofUnprocessedEnvelopes] ,
         COUNT (   CASE WHEN dq.AssignedToUserID = 0 THEN 1
                        ELSE NULL
                   END
               ) AS [#ofUnassignedEnvelopes] ,
         CONVERT ( VARCHAR (8), MIN ( dq.DocumentDate ), 1 ) AS [OldestEnvelope]
FROM     QueueTypes AS qt
         LEFT OUTER JOIN dbo.VehicleReg_Documents_Queue AS dq ON dq.QueueID = qt.QueueTypeID
WHERE    dq.IsProcessed = 0
         AND dq.PageNumber = 1
GROUP BY qt.QueueName
UNION ALL
SELECT   qt.QueueName AS [Queue] ,
         COUNT ( qt.QueueName ) AS [#ofUnprocessedEnvelopes] ,
         COUNT (   CASE WHEN dq.AssignedToUserID = 0 THEN 1
                        ELSE NULL
                   END
               ) AS [#ofUnassignedEnvelopes] ,
         CONVERT ( VARCHAR (8), MIN ( dq.DocumentDate ), 1 ) AS [OldestEnvelope]
FROM     QueueTypes AS qt
         LEFT OUTER JOIN dbo.VehicleReg_Documents_Queue AS dq ON dq.QueueID = qt.QueueTypeID
GROUP BY qt.QueueName

But the results are not close to being correct:

Answer 1

Your current query doesn't work as it seems to work, because while you outer join table VehicleReg_Documents_Queue you dismiss all outer joined rows in the WHERE clause, so you are where you would have been with a mere inner join. You may want to consider either moving your criteria to the ON clause or make this an inner join right away.

It is also weird that you join queue type and queue not on the queue ID or the queue type ID, but on dq.QueueID = qt.QueueTypeID. That's like joining employees and addresses on employee number matching the house number. At least that's what it looks like.

(Then why does your queue type table have a queue name? Shouldn't the queue table contain the queue name instead? But this is not about your query, but about your data model.)

GROUP BY ALL means: "Please give us all QueueNames, even when the WHERE clause dismisses them. I see two possibilities for your query:

1. You do want an outer join actually. Then there is no WHERE clause and you can simply make this GROUP BY qt.QueueName.
2. You don't want an outer join. Then we want a row per QueueName in the table, which we might not get with simply changing GROUP BY ALL qt.QueueName to GROUP BY qt.QueueName.

In that second case we want all QueueNames first and outer join your query:

select 
  qn.QueueName AS [Queue],
  q.[#ofUnassignedEnvelopes],
  q.[OldestEnvelope]
FROM (select distinct QueueName from VehicleReg_Documents_QueueTypes) qn
LEFT JOIN
(
  SELECT       qt.QueueName,
               COUNT ( qt.QueueName ) AS [#ofUnprocessedEnvelopes] ,
               COUNT (   CASE WHEN dq.AssignedToUserID = 0 THEN 1
                              ELSE NULL
                         END
                     ) AS [#ofUnassignedEnvelopes] ,
               MIN ( dq.DocumentDate ) AS [OldestEnvelope]
  FROM         dbo.VehicleReg_Documents_QueueTypes AS [qt]
  JOIN         dbo.VehicleReg_Documents_Queue AS [dq] ON dq.QueueID = qt.QueueTypeID
  WHERE        dq.IsProcessed = 0
               AND dq.PageNumber = 1
) q ON q.QueueName = qn.QueueName
GROUP BY ALL qn.QueueName
ORDER BY     qn.QueueName ASC;