Count Query in SQL

Question

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

araqnid · Accepted Answer · 2009-04-17 18:27:55Z

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.

answered Apr 17, 2009 at 18:27

araqnid

135k25 gold badges164 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Greg Beech · Accepted Answer · 2009-04-17 18:25:39Z

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;

answered Apr 17, 2009 at 18:25

Greg Beech

137k45 gold badges210 silver badges249 bronze badges

5 Comments

ErikE Over a year ago

Why would you use a correlated subquery, merely hoping that the engine converts it to a sensible execution plan, rather than putting it in as a derived table and knowing? The correlated subquery only makes sense if you're querying a small set of rows through a WHERE clause in the outer (or inner) query.

Greg Beech Over a year ago

@Erik - I just illustrated a fairly simple way to achieve what was asked. Without knowing whether performance is actually an issue, there seems little point in trying to optimise it.

ErikE Over a year ago

Hmmm. Given #1 how bad correlated subqueries can perform and #2 that I see many beginners struggle with set-based conceptual issues that seem to guide them to correlated subqueries over derived tables (and IN() over proper JOINs), I just don't use correlated subqueries unless I know they are going to help performance-wise. To me your query is the one doing premature optimization.

Greg Beech Over a year ago

@Erik - I'm always interested in seeing how other people would approach a problem; why don't you leave an answer showing how you would do it?

ErikE Over a year ago

Good point. I suppose presenting a full array of potential solutions is good, though it's always nice to see caveats given when they are present. See the answer from @araqnid for the derived table version, including a helpful caveat about when to switch to a correlated subquery...

Ben S · Accepted Answer · 2009-04-17 18:29:08Z

0

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

answered Apr 17, 2009 at 18:29

Ben S

69.6k32 gold badges175 silver badges214 bronze badges

Comments

ErikE · Accepted Answer · 2011-05-04 16:29:14Z

0

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;

edited May 4, 2011 at 16:29

ErikE

50.7k23 gold badges157 silver badges201 bronze badges

answered Apr 17, 2009 at 18:26

Chad Birch

75k24 gold badges156 silver badges150 bronze badges

Comments

ErikE · Accepted Answer · 2011-05-04 16:30:22Z

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

Collectives™ on Stack Overflow

Count Query in SQL

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related