Count Query in SQL

Question

Hi. I am using SQL 2005.

Table one, LinkedDocumentFolderTable, contains the names and information about the folders that hold linked documents.

Table two, LinkedDocumentTable, contains the information about the linked documents themselves.

The two tables are linked by the LinkedDocumentFolderID.

I need the LinkedDocumentFolderID, Description, FolderURL and IsUnmanagedFolder from the LinkedDocumentFolderTable. I would also like to get a count of the number of linked documents in each folder (identified by the linkeddocumentfolderid).

The statement below works from the perspective of giving me an overall count of documents in the LinkedDocumentTable, but doesn't break it out by linkeddocumentfolderid. Please help me re-write the statement to get a count of the linkeddocuments in each folder. Thanks in advance.

select Count(*)
from linkeddocumenttable
    select ld.linkeddocumentfolderid,ld.description,
    ld.folderURL,ld.isunmanagedfolder
    from linkeddocumentfoldertable ld
        inner join linkeddocumenttable l on
        ld.linkeddocumentfolderid=l.linkeddocumentfolderid

Answer 1

I think what you're looking for to get the count of documents per folder is something like:

SELECT
    LinkedDocumentFolderID
    ,COUNT(*) AS DocumentCount
FROM
    LinkedDocumentTable
GROUP BY
    LinkedDocumentFolderID;

If you're actually trying to get this as a single statement, then you could use a correlated sub-query, e.g.

SELECT 
    ld.LinkedDocumentFolderID
    ,ld.Description
    ,ld.FolderURL,
    ,ld.IsUnmanagedFolder
    ,DocumentCount =
         (SELECT COUNT(*) 
          FROM LinkedDocumentTable l
          WHERE l.LinkedDocumentFolderID = ld.LinkedDocumentFolderID)
FROM
    LinkedDocumentFolderTable ld;

Answer 2

Providing I follow correctly, this should do the job:

SELECT
 f.LinkedDocumentFolderID,
 f.Description,
 f.FolderURL,
 f.IsUnmanagedFolder,
 (SELECT COUNT(*) FROM LinkedDocumentTable d WHERE d.LinkedDocumentFolderID = f.LinkedDocumentFolderID) NumDocuments
FROM LinkedDocumentFolderTable f
ORDER BY f.LinkedDocumentFolderID;

Answer 3

select LinkedDocumentFolderTable.LinkedDocumentFolderID, Description,
    FolderURL, IsUnmanagedFolder, DocumentCount
from LinkedDocumentFolderTable
join (select count(*) as DocumentCount, LinkedDocumentFolderID
    from LinkedDocumentTable
    group by LinkedDocumentFolderID) stats
on LinkedDocumentFolderTable.LinkedDocumentFolderID = stats.LinkedDocumentFolderID

There's probably little difference between this and the correlated subquery version others have suggested. A quick test in postgres shows they do have different plans, and I like to go for uncorrelated queries if possible. On the other hand, if you were limiting which folders you were looking at, a correlated query could involve scanning a lot less of the document table.

Answer 4

In a sub-query, get the count of how many documents there are for each LinkedDocumentFolderID, then join those to the rest of the info you want.

SELECT LinkedDocumentFolderID, Description, FolderURL, IsUnmanagedFolder, Num_Docs
FROM LinkedDocumentFolderTable, (
    SELECT LinkedDocumentFolderID, COUNT(*) AS Num_Docs
    FROM LinkedDocumentFolderTable folders, LinkedDocumentTable docs 
    WHERE folders.LinkedDocumentFolderID=docs.LinkedDocumentFolderID
    GROUP BY LinkedDocumentFolderID
) AS DocsPerFolder
WHERE DocsPerFolder.LinkedDocumentFolderID=LinkedDocumentFolderTable.LinkedDocumentFolderID

Answer 5

Do you need them in the same query? The reason I'm asking is because if you do, you're providing a count of the number of documents in a folder for every document.

Assuming that you do, let's layout what you've got here (this may not be exactly what you described, but it'll make a good example):

LinkedDocumentFolder -- ID, FolderName

LinkedDocument -- ID, LinkedDocumentFolderID, Description

The first thing, is that you're after a list of documents, which of course is:

SELECT * FROM LinkedDocument

Now, since you also want the folder Information, you'll need to join that in:

SELECT ldf.FolderName, ld.* FROM LinkedDocument ld INNER JOIN LinkedDocumentFolder ldf ON ldf.ID = ld.LinkedDocumentFolderID

Now, the fun part. Since our assumption is that you want the list of documents included with every record of the returned dataset, what you're needing now, is to also include a count with that particular list. What you want will be to add in another join, which works on just the counts:

SELECT ldf.FolderName, ld.* FROM LinkedDocument ld INNER JOIN LinkedDocumentFolder ldf ON ldf.ID = ld.LinkedDocumentFolderID 
INNER JOIN (SELECT LinkedDocumentFolderID, COUNT(ID) AS DocCount FROM LinkedDocument GROUP BY LinkedDocumentFolderID) AS CNT ON cnt.LinkedDocumentFolderID = ldf.ID