How to create a function/method which counts how may files are within a particular folder – T-SQL

I need help creating some kind of function/method to count how many files are in a particular folder. Here is an example sample data set I am using:

As you can see from the sample data set, the folder: "Computer\User01\MyDocuments" contains 4 files. I could write the following code to show how many files are in this folder:

 SELECT COUNT(*) AS No_of_files
 FROM SampleDataSet
 WHERE Type = 'File'
 AND FullPath LIKE 'Computer\User01\MyDocuments\%'

However, my data set contains hundreds of thousands of folders, all with a different number of files associated with them and therefore I can’t specify the "FullPath" in the LIKE statement each time.

My desired output looks like this:

Does anyone know if this is possible and if there’s an efficient way of doing this?

Any help would be much appreciated, thanks!

>Solution :

Seems that you could use a JOIN from the table onto itself to achieve this. Something like this:

SELECT D.FullPath,
       D.[Type],
       COUNT(F.FullPath) AS Files
FROM dbo.YourTable D
     LEFT JOIN dbo.YourTable F ON F.FullPath LIKE D.FullPath + '%'
                              AND F.[Type] = 'File'
WHERE D.[Type] = 'Folder'
GROUP BY D.FullPath,
         D.[Type];