Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate how to obtain frequency only for a subset of first names.

-- SQL common table expression - t-sql cte - MSSQL group by count
USE AdventureWorks;
WITH cteFirstNameFrequency
     AS (SELECT   FirstName,
                  NameFrequency = count(* )
         FROM     Person.Contact
         WHERE    FirstName IN ('Peter','Jane','Kathleen','John')
         GROUP BY FirstName
         UNION
         SELECT   FirstName,
                  NameFrequency = 0
         FROM     Person.Contact
         WHERE    FirstName NOT IN ('Peter','Jane','Kathleen','John')
         GROUP BY FirstName)
SELECT   FirstName,
         NameFrequency
FROM     cteFirstNameFrequency
ORDER BY NameFrequency DESC,
         FirstName
GO
/* Partial results
 
FirstName   NameFrequency
John        58
Peter       28
Kathleen    22
Jane        5
A.          0
A. Scott    0
Aaron       0
Abby        0

*/

------------