SQL Server 2019

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

CREATE TABLE Clients
(
OID INT,
Client_name VARCHAR(20) NOT NULL,
Client_central VARCHAR(20) NOT NULL,
Client_assigned_to INT NOT NULL,
);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (1, 'Client_1', 'True', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (2, 'Client_2', 'False', 1);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (3, 'Client_3', 'True', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (4, 'Client_4', 'False', 2);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (5, 'Client_5', 'False', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (6, 'Client_6', 'False', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (7, 'Client_7', 'False', 1);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (8, 'Client_8', 'False', 2);

SELECT

Clients.OID,

CASE 
WHEN Clients.Client_central = 'True' THEN Clients.Client_name
WHEN Clients.Client_central = 'False' AND Clients.Client_assigned_to IS NULL THEN Clients.Client_name
ELSE Clients1.Client_name
END AS Central,

ROW_NUMBER() OVER(Partition by

CASE 
WHEN Clients.Client_central = 'True' THEN Clients.Client_name
WHEN Clients.Client_central = 'False' AND Clients.Client_assigned_to IS NULL THEN Clients.Client_name
ELSE Clients1.Client_name
END

) AS Row_Number,

(SELECT Count(

CASE 
WHEN Clients.Client_central = 'True' THEN Clients.Client_name
WHEN Clients.Client_central = 'False' AND Clients.Client_assigned_to IS NULL THEN Clients.Client_name
ELSE Clients1.Client_name
END

)

OVER(Partition by

CASE 
WHEN Clients.Client_central = 'True' THEN Clients.Client_name
WHEN Clients.Client_central = 'False' AND Clients.Client_assigned_to IS NULL THEN Clients.Client_name
ELSE Clients1.Client_name
END

) ) AS Central_Count

FROM
Clients
LEFT OUTER JOIN Clients Clients1 ON (Clients.Client_assigned_to = Clients1.OID)

OID	Central	Row_Number	Central_Count
5	null	1	0
6	null	2	0
7	Client_1	1	1
1	Client_1	2	1
2	Client_1	3	1
4	Client_2	1	1
8	Client_2	2	1
3	Client_3	1	1

Warning: Null value is eliminated by an aggregate or other SET operation.