[SOLVED] Teradata SQL to see if customer has unique National Insurance Number

Teradata SQL to see if customer has unique National Insurance Number

I have a list of customers and each customer should have a distinct national insurance number assigned. Some customers will have the same NIN. How do I write the query to check whether each customer has a unique NIN?

This is what I've tried, later in the script I aggregate the total of those that are unique.

SUM(CASE WHEN Natinsnbr is not null and natinsnbr <> '' THEN 1 END)
OVER (PARTITION BY NINNBR ORDER BY UPDATEDTTM DESC) 
AS UNI_NINNBR

Sample Data:

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

Output would then hopefully look like:

customer number	ninnbr	Unique
123	AA111111AA	3
456	AA111111AA	3
678	AA111111AA	3
910	AB123456AB	1
109	AC123456AB	1
549	AD123456AD	2
295	AD123456AD	2
743	AE123456AE	1
443	AF123456AF	1
903	AG123456AG	1

Later in the script I'll aggregate based on unique = 1

Solution

I have tried in mysql based on sample data and expected output, syntax should be very similar to Terradata.

Fiddle

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;

Output