By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.MyTable
(
Col_A char(1) NOT NULL,
Col_B int NULL
);
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
-- 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 | null | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.