MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


CREATE TABLE customers (
    customer_number INT,
    ninnbr VARCHAR(12),
    PRIMARY KEY (customer_number)
);
INSERT INTO customers (customer_number, ninnbr) VALUES
(123, 'AA111111AA'),
(456, 'AA111111AA'),
(678, 'AA111111AA'),
(910, 'AB123456AB'),
(109, 'AC123456AB'),
(549, 'AD123456AD'),
(295, 'AD123456AD'),
(743, 'AE123456AE'),
(443, 'AF123456AF'),
(903, 'AG123456AG');

Records: 10  Duplicates: 0  Warnings: 0


select * from customers ;


customer_number
ninnbr


109
AC123456AB

123
AA111111AA

295
AD123456AD

443
AF123456AF

456
AA111111AA

549
AD123456AD

678
AA111111AA

743
AE123456AE

903
AG123456AG

910
AB123456AB


SELECT 
    c.customer_number,
    c.ninnbr,
    COUNT(*) OVER (PARTITION BY c.ninnbr) AS unique_count
FROM 
    customers c
ORDER BY 
    c.ninnbr, c.customer_number;


customer_number
ninnbr
unique_count


123
AA111111AA
3

456
AA111111AA
3

678
AA111111AA
3

910
AB123456AB
1

109
AC123456AB
1

295
AD123456AD
2

549
AD123456AD
2

743
AE123456AE
1

443
AF123456AF
1

903
AG123456AG
1

customer_number	ninnbr
109	AC123456AB
123	AA111111AA
295	AD123456AD
443	AF123456AF
456	AA111111AA
549	AD123456AD
678	AA111111AA
743	AE123456AE
903	AG123456AG
910	AB123456AB