How to count one to many relationships

ReporterTbl has a one to many relationship with AttachmentTbl.

In ReporterTbl, I have an ID (101) and I can have AttachmentTbl more than one Attachments related with ReporterTbl.Id

SELECT     
ISNULL(ReporterTbl.Id, 0) AS Id, 
CONVERT(char(10), ReporterTbl.StartDate, 101) AS StartDate, 
ISNULL(ReporterTbl.Prior开发者_运维技巧ityId, 0) AS PriorityId, 
ISNULL(dbo.ReporterTbl.PriorityDesc, '') AS PriorityDesc, 
 (select       
   ReporterTbl.Id, 
   COUNT(dbo.AttachmentTbl.Id) AS attachment_Id
FROM         
dbo.AttachmentTbl RIGHT OUTER JOIN
ReporterTbl ON dbo.AttachmentTbl.Id = ReporterTbl.Id
GROUP BY ReporterTbl.Id) AS IsAttachment
)

Basically, what I am trying to know is given ReporterTbl.ID, how many Attachments do I have?

Table structure:

 ReporterTbl

    Id int   {**PrimaryKey**}
    StartDate datetime
    PriorityId int
    PriorityDesc varchar(500

    AttachmentTbl:

    AttachmentId indentity
    Id {**FK to ReproterTbl**}
    Filename
    Content
    ...

select r.id, count(a.id) as Count
from ReporterTbl r
left outer join AttachmentTbl a on r.id = a.id
group by r.id

Note: It is important that we are using count(a.id), and not count(*). This is because count will ignore null values when we count the results of an expression. If instead we use count(*), SQL will count the resulting rows, so any rows from ReporterTbl that don't have a matching row in AttachmentTbl would return a count of 1 because we still return the row due to the left join.

If you want to get all fields from Reported (not only ID), this will save you a JOIN:

SELECT  r.*,
        (
        SELECT  COUNT(*)
        FROM    AttachmentTbl a
        WHERE   a.id = r.id
        ) AS AttachmentCount
FROM    ReportedTbl r

given ReporterTbl.ID how many attachments i have.

Wouldn't it just be:

select count(*) from AttachmentTbl where id = @ID;

I had to group by the first element in the SELECT clause as well as the item I am aggregating on:

SELECT p.Name, COUNT(c.PersonId) AS Count
FROM People AS p
LEFT JOIN Contacts AS c
    ON (p.Id = c.PersonId)
GROUP BY c.PersonId, p.Name;

I am not really sure why I had to do this, it isn't the case when using SQLite, which is what I am used to.