-- initial version
SELECT
Col_A,
Col_B,
DistinctCount = DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
+ DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
- 1
- CASE COUNT(Col_B) OVER (PARTITION BY Col_A)
WHEN COUNT( * ) OVER (PARTITION BY Col_A)
THEN 0
ELSE 1
END
FROM
dbo.MyTable
;
Col_A
Col_B
DistinctCount
A
3
3
A
2
3
A
2
3
A
2
3
A
1
3
A
1
3
b
5
2
b
4
2
b
4
2
C
6
1
C
6
1
C
1
…
Warning: Null value is eliminated by an aggregate or other SET operation.
hidden batch(es)
-- "enhanced" version
SELECT
Col_A,
Col_B,
DistinctCount = DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
+ DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
- CASE COUNT(Col_B) OVER (PARTITION BY Col_A)
WHEN COUNT( * ) OVER (PARTITION BY Col_A)
THEN 1
ELSE 2
END
FROM
dbo.MyTable
;
Col_A
Col_B
DistinctCount
A
3
3
A
2
3
A
2
3
A
2
3
A
1
3
A
1
3
b
5
2
b
4
2
b
4
2
C
6
1
C
6
1
C
1
…
Warning: Null value is eliminated by an aggregate or other SET operation.
hidden batch(es)
-- Erik Darling's solution, for verification
SELECT *
FROM dbo.MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
FROM dbo.MyTable AS mt2
WHERE mt2.Col_A = mt.Col_A
-- GROUP BY mt2.Col_A
) AS ca;
Col_A
Col_B
dc
A
1
3
A
1
3
A
2
3
A
2
3
A
2
3
A
3
3
b
4
2
b
4
2
b
5
2
C
6
1
C
6
1
C
1
…
Warning: Null value is eliminated by an aggregate or other SET operation.
