Calculating count on same table based on condition So I need to calculate the amount of Clients who's LastName start with the same letter, then use that count as part of an Reference number. 

I.e. something like this:

declare @AmountOfSimilarSurnames int;
set @AmountOfSimilarSurnames = (...)

declare @Ref nvarchar(100);
set @Ref = 'CLT00' + @AmountOfSimilarSurnames


I need help setting that AmountOfSimilarSurnames

So far I got this but it is working:

select COUNT(*) FROM Client c1
join Client c2 on RIGHT(c1.Client_LName,1) = RIGHT(c2.Client_LName,1)
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));