DQL to find the count of documents in each folder location under /Cabinet/FolderA/FolderB.

Linked documents can definitely affect your folder count. Below is a modified version of your query to help identify where these linked documents might be located:

SELECT fdr.r_folder_path, COUNT(doc.r_object_id) as doccnt,
sum(doc.i_reference_cnt) as refcnt, sum(doc.r_full_content_size)/1024 as sizeKB
FROM dm_document (ALL) doc, dm_folder_r fdr
WHERE ANY doc.i_folder_id = fdr.r_object_id
AND fdr.r_folder_path like ‘/Temp%’
GROUP BY fdr.r_folder_path ORDER BY fdr.r_folder_path

result looks like:

The difference between doccnt and refcnt indicates folders where documents are linked. In this case, the /Temp and /Temp/Jobs/dm_Consistency Checker folders contain linked documents.

For more insight, the following query shows which documents are linked and the folders they are linked to:

SELECT doc.r_object_id, doc.object_name, fdr.r_folder_path,doc.i_folder_id
FROM dm_document (ALL) doc, dm_folder_r fdr
WHERE ANY doc.i_folder_id = fdr.r_object_id
AND fdr.r_folder_path like '/Temp%'
AND doc.i_reference_cnt > 1
ORDER BY fdr.r_folder_path ENABLE(ROW_BASED)


Here is how to get a count of all the documents in a cabinet by folder:


select count(*), f.r_folder_path from dm_document d, dm_folder f
where d.i_folder_id = f.r_object_id and folder('/CABINET_NAME', descend)
and f.r_folder_path is not nullstring
group by f.r_folder_path
enable(row_based)

looking for are folders which may contain other folders, but none of the sub-folders should have a document.

select  r_object_id, object_name, r_folder_path, r_creation_date, r_modify_date
from dm_folder //or custom dm_folder subtype
where r_object_id not in   //Returns all folders which are not in the list of ancestor folders (folder hierarchy) as returned below
(
    select distinct (i_ancestor_id) from dm_folder where r_object_id in   //Returns all ancestor folders in the hierarchy of the parent folders below
               (
        select distinct(i_folder_id) from dm_document WHERE <your custom where clause>   //Returns all parent folders which contain documents 
               )
)
and any r_folder_path is not nullstring
and folder('/Background Investigation', descend)
enable (ROW_BASED)  //Needed when querying repeating attibutes such as r_folder_path above