DECLARE @tbl1 TABLe

(Id INT,

Name VARCHAR(100))

INSERT INTO @tbl1

SELECt 1,'soemthing here' UNION ALL

SELECT 2,'antoher here' UNION ALL

SELECT 3,'should have 0 count'

DECLARE @tbl2 TABLE

(id int,

Col2 VARCHAR(10))

INSERT INTO @tbl2

SELECT 1,'a' UNION ALL

SELECT 1,'a' UNION ALL

SELECT 1,'a' UNION ALL

SELECT 1,'a' UNION ALL

SELECT 1,'a' UNION ALL

SELECT 2,'b' UNION ALL

SELECT 2,'b' UNION ALL

SELECT 2,'b'

SELECT

a.Name,

COUNT(b.Col2) as [COUNT]

FROM @tbl1 a

LEFT JOIN @tbl2 b ON a.id = b.Id

GROUP BY

a.Name