CODEWITHSUNDEEP
sqlsql-server-2008
Nick Kahn
Nick Kahn

Reputation: 20078

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.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
    ...

Upvotes: 24

Views: 30810

Answers (4)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171471

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.

Upvotes: 44

Michael Murphy
Michael Murphy

Reputation: 1981

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425533

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

Upvotes: 13

James Curran
James Curran

Reputation: 103525

given ReporterTbl.ID how many attachments i have.

Wouldn't it just be:

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

Upvotes: 3

Related Questions