Calculating count on same table based on condition
Below is an example using RIGHT (starting character) and EXISTS (so that matching names are counted only once).

DECLARE @Ref nvarchar(100) = 'CLT00'
    + CAST(
        (SELECT COUNT(*)
        FROM Client c1
        WHERE EXISTS(
            SELECT 1
            FROM Client c2
            WHERE
                LEFT(c1.Client_LName,1) = LEFT(c2.Client_LName,1)
            )
        ) AS varchar(10));