SQL Sever Getting Distinct Count using "Group By ... With Cube"

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 (as stated here)

Here is what the Data Looks Like. (This is just a simple example I do expect duplicates in the Data)

    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

Here is what I would like to do but it doesn't work.

SELECT
  Category1,
  Category2,
  Count(Distinct(ItemId))
FROM ItemList IL
GROUP BY
  Category1,
  Category2
WITH CUBE

I know that I can do a sub select like this to get the results I want:

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

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?

This is the result I want to see

Category1     Category2    DistinctCountOfItems
a             a            3
a             b            3
a             c            3
a             NULL         4
NULL          NULL         4
NULL          a     开发者_如何学Go       3
NULL          b            3
NULL          c            3

You should be able to clean up your "messy" answer like so:

select Category1, Category2, count(distinct ItemId)
from ItemList
group by Category1, Category2
UNION ALL
select Category1, null, count(distinct ItemId)
from ItemList
group by Category1
UNION ALL
select null, Category2, count(distinct ItemId)
from ItemList
group by Category2
UNION ALL
select null, null, count(distinct ItemId)
from ItemList

Then the other option I came up with:

select IL1.Category1, IL1.Category2, count(distinct ItemId)
from ( 
  select Category1, Category2
  from ItemList
  group by Category1, Category2
  with cube
 ) IL1
 join ItemList IL2 on (IL1.Category1=IL2.Category1 and IL1.Category2=IL2.Category2)
      or (IL1.Category1 is null and IL1.Category2=IL2.Category2)
      or (IL1.Category2 is null and IL1.Category1=IL2.Category1)
      or (IL1.Category1 is null and IL1.Category2 is null)
group by IL1.Category1, IL1.Category2

The efficiency may vary based on the indexes, number of columns being grouped, etc. For the test table I wrote, the sub-select and join (as opposed to the Unions) was slightly better. I don't have access to a MSSQL 2000 instance at the moment (I tested on a 2005 instance), but I don't think anything here is invalid.

UPDATE

An even better option, especially if you're grouping on more than 2 columns (If you're grouping on 8 columns the above code would require 256 join clauses to catch all null combinations!):

select IL1.Category1, IL1.Category2, count(distinct ItemId)
from ( 
  select Category1, Category2
  from ItemList
  group by Category1, Category2
  with cube
 ) IL1
 inner join ItemList IL2 on isnull(IL1.Category1,IL2.Category1)=IL2.Category1
                  and isnull(IL1.Category2,IL2.Category2)=IL2.Category2
group by IL1.Category1, IL1.Category2

Here is another possibility I found but it is extremely messy. However it runs faster than using a subselect.

SELECT 
  category1, 
  category2, 
  count(distinct itemid)
FROM (SELECT DISTINCT 
        category1, 
        category2, 
        itemid
      FROM ItemList
) x
GROUP BY category1, category2
UNION ALL
SELECT 
  category1, 
  NULL, 
  count(distinct itemid)
FROM (SELECT DISTINCT 
        category1, 
        category2, 
        itemid
      FROM ItemList
) x
GROUP BY category1
UNION ALL
SELECT 
  NULL, 
  category2, 
  count(distinct itemid)
FROM (SELECT DISTINCT 
        category1, 
        category2, 
        itemid
      FROM ItemList
) x
GROUP BY category2
UNION ALL
SELECT 
  NULL, 
  NULL, 
  count(distinct itemid)
FROM (SELECT DISTINCT 
        category1, 
        category2, 
        itemid
      FROM ItemList
) x

That is very interesting. I can run your first query in SQL Server 2008 R2, yet the documentation says it won't work.

Here's a variation of your second query that may perform better. It does the distinct count in the sub query and the cube in the outer query

SELECT Category1, Category2, MAX(DistinctCount) as DistinctCount
FROM (
    SELECT Category1, Category2, COUNT(DISTINCT ItemId) as DistinctCount
    FROM ItemList
    GROUP BY Category1, Category2
    ) Q1
GROUP BY Category1, Category2
WITH CUBE

How about this??

The inner query would return distinct result.

SELECT ORIGINAL_ITEM.Category1, DISTINCT_ITEM.Category2, DISTINCT_ITEM.cnt
FROM
  ( SELECT DISTINCT category2, COUNT(*) as CNT
    FROM ItemList ) DISTINCT_ITEM
JOIN ItemList ORIGINAL_ITEM on ORIGINAL_ITEM.category2 = DISTINCT_ITEM.category2 
GROUP BY ORIGINAL_ITEM.category1, DISTINCT_ITEM.category2 

I have the following version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)

When I run your query

SELECT 
  Category1, 
  Category2, 
  COUNT(DISTINCT(ItemId))
FROM ItemList IL 
GROUP BY 
  Category1, 
  Category2 
WITH CUBE 

I Get these results

a       a       3
a       b       3
a       c       3
NULL    a       3
NULL    b       3
NULL    c       3
a       NULL    4
NULL    NULL    4