clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648565 fiddles created (15289 in the last week).

CREATE TABLE dbo.MyTable ( Col_A char(1) NOT NULL, Col_B int NULL );
 hidden batch(es)


INSERT INTO dbo.MyTable (Col_A, Col_B) VALUES ('A', 1), ('A', 1), ('A', 2), ('A', 2), ('A', 2), ('A', 3), ('b', 4), ('b', 4), ('b', 5), ('C', 6), ('C', 6), ('C', NULL) ;
12 rows affected
 hidden batch(es)


-- 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.
 hidden batch(es)