Or you can try something like this: SELECT sub.surname,count(*) as count FROM (select top 5 surname from myTable) sub GROUP BY sub.surname --[WHERE surname = 'CRUZ'] OUTPUT will bev [without the where clause] CRUZ 3 TOM 1 JACK 1 if you want to see all 5 rows [basically repeating ] SELECT sub3.surname,sub2.count FROM (select count(*) as count from (select top 5 surname from myTable where surname = 'CRUZ') sub) sub2 CROSS JOIN (select top 5 column_name from myTable where surname = 'CRUZ' ) sub3