title: SQL Sever Getting Distinct Count using "Group By ... With Cube"
body: <p>Basically I'm trying to get a distinct count within this cubed result. But unfortuantly you cannot use Count(distinct(Field)) with cube and rollup (<a href="http://msdn.microsoft.com/en-us/library/ms177673.aspx" rel="nofollow">as stated here</a>)</p>
  
  <p>Here is what the Data Looks Like.  (This is just a simple example I do expect duplicates in the Data)</p>
  
  <pre><code>    Category1       Category2       ItemId
      a               b               1
      a               b               1
      a               a               1
      a               a               2
      a               c               1
      a               b               2
      a               b               3
      a               c               2
      a               a               1
      a               a               3
      a               c               4
  </code></pre>
  
  <hr>
  
  <p>Here is what I would like to do but it doesn't work.</p>
  
  <pre><code>SELECT
    Category1,
    Category2,
    Count(Distinct(ItemId))
  FROM ItemList IL
  GROUP BY
    Category1,
    Category2
  WITH CUBE
  </code></pre>
  
  <p>I know that I can do a sub select like this to get the results I want:</p>
  
  <pre><code>SELECT
    *,
    (SELECT
       Count(Distinct(ItemId)) 
     FROM ItemList IL2 
     WHERE 
       (Q1.Category1 IS NULL OR Q1.Category1 IS NOT NULL AND Q1.Category1 = IL2.Category1) 
       AND
       (Q1.Category2 IS NULL OR Q1.Category2 IS NOT NULL AND Q1.Category2 = IL2.Category2))
         AS DistinctCountOfItems 
  FROM (SELECT
          Category1,
          Category2
        FROM ItemList IL
        GROUP BY
          Category1,
          Category2
        WITH CUBE) Q1
  </code></pre>
  
  <p>But this runs slow when the result set is large due to the sub-select.  Is there any other way to get a Distinct Count from a cubed result?</p>
  
  <p>This is the result I want to see</p>
  
  <pre><code>Category1     Category2    DistinctCountOfItems
  a             a            3
  a             b            3
  a             c            3
  a             NULL         4
  NULL          NULL         4
  NULL          a            3
  NULL          b            3
  NULL          c            3
  </code></pre>