CODEWITHSUNDEEP
sqlsql-server
almasar group
almasar group

Reputation: 1

How to find the count of a column in a child table related to a main table

I have a sub table linked to a master table in a SQL Server database and I want to count the number of attachments from the sub table for each file in the master table, I made this query but it gives me an error.

This is my code:

SELECT  
    tblFiles.FileID, tblFiles.FileName, tblFiles.FileNotes, 
    COUNT (tblAttaches.AttachID) 
FROM
    tblAttaches 
INNER JOIN
    tblFiles ON tblAttaches.FileID = tblFiles.FileID 
WHERE 
    tblFiles.FileID = 1

Upvotes: 0

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Assuming you want to find the number of attachments per file, you should be aggregating by file:

SELECT f.FileID, f.FileName, f.FileNotes, COUNT(a.AttachID) AS num_attachments
FROM tblFiles f
LEFT JOIN tblAttaches a
    ON a.FileID = f.FileID
-- WHERE f.FileID = 1
GROUP BY f.FileID, f.FileName, f.FileNotes;

You may uncomment the WHERE clause to restrict to one or more particular files.

Upvotes: 1

Related Questions