title: t-sql GROUP BY with COUNT, and then include MAX from the COUNT
body: <p>Suppose you had a table of "Cars" with hundreds of thousands of rows, 
  and you wanted to do a GROUP BY: </p>
  
  <pre><code>SELECT   CarID
           , CarName
           , COUNT(*) AS Total
  FROM     dbo.tbl_Cars
  GROUP BY CarID
           , CarName
  </code></pre>
  
  <p>The grouping leaves you with a result akin to:</p>
  
  <pre><code>CarID       CarName    Total
  1872        Olds       202,121   
  547841      BMW        175,298
  9877        Ford        10,241
  </code></pre>
  
  <p>All fine and well. 
  My question, though, is what is the best way to get the 
  Total and the MAX Total into one table, in terms of performance and 
  clean coding, so you have a result like: </p>
  
  <pre><code>CarID       CarName    Total      Max Total
  1872        Olds       202,121    202,121
  547841      BMW        175,298    202,121
  9877        Ford        10,241    202,121 
  </code></pre>
  
  <p>One approach would be to put the GROUP result into a temp table, 
  and then get the MAX from the temp table into a local variable.
  But I'm wondering what the best way to do this would be. </p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>The Common Table Expression seems the most elegant to write,
  yet similar to @EBarr, my limited testing indicates a significantly slower performance.
  So I won't be going with the CTE. </p>
  
  <p>As the link @EBarr has for the <code>COMPUTE</code> option indicates the feature
  is deprecated, that doesn't seem the best route, either. </p>
  
  <p>The option of a local variable for the MAX value and the use of 
  a temp table will likely be the route I go down, as I'm not 
  aware of performance issues with it. </p>
  
  <p>A bit more detail about my use case: it could probably end up being a 
  series of other SO questions. But suffice to say that I'm loading 
  a large subset of data into a temp table (so a subset of tbl_Cars is
  going into #tbl_Cars, and even #tbl_Cars may be further filtered
  and have aggregations performed on it), because I have to perform multiple filtering 
  and aggregation queries on it within a single stored proc 
  that returns multiple result sets. </p>
  
  <hr>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>@EBarr's use of a windowed function is nice and short. Note to self: 
  if using a <code>RIGHT JOIN</code> to an outer reference table, the <code>COUNT()</code> 
  function should select a column from tbl_Cars, not <code>'*'</code>. </p>
  
  <pre><code>SELECT       M.MachineID
               , M.MachineType
               , COUNT(C.CarID) AS Total
               , MAX(COUNT(C.CarID)) OVER() as MaxTotal
  FROM         dbo.tbl_Cars C
  RIGHT JOIN   dbo.tbl_Machines M
        ON     C.CarID = M.CarID
  GROUP BY     M.MachineID
               , M.MachineType
  </code></pre>
  
  <p>In terms of speed, it seems fine, but at what point do you have to be 
  worried about the number of reads?</p>