DQL to find out current version documents in a folder

Question

How to query folder and document both so that we can get:

1. All the subfolder documents.
2. Only the folder documents.
3. Count of documents in the subfolder, with the folder name.

Answer 1

I took the liberty to name queries a bit differently than OP asked it, but it fits it too. It is just a bit more clearer what some query returns. I used Temp folder for this example.

1. All documents in folder including subfolders

SELECT *
FROM dm_document 
WHERE FOLDER('/Temp', DESCEND)

2. All documents in folder

SELECT * 
FROM dm_document 
WHERE FOLDER('/Temp')

3. Count of documents in specific subfolders under some folder

SELECT COUNT(doc.r_object_id) AS doc_ammount, fol.object_name AS folder_object_name 
FROM dm_document doc, dm_folder fol 
WHERE FOLDER('/Temp', descend) AND ANY doc.i_folder_id = fol.r_object_id 
GROUP BY (fol.object_name)