How to count one to many relationships

user_275390 0
Viewed 22884

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.PriorityId, 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
    ...
sql-server-2008 sql
4 Answers

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.

user_19917350
Related