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

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:

FullPath	Type	Age (Years)
Computer\User01\MyDocuments\	Folder	4
Computer\User01\MyDocuments\thisisafile.xlsx	File	2.2
Computer\User01\MyDocuments\anotherfile.doc	File	1
Computer\User01\MyDocuments\onemorefile.doc	File	1.5
Computer\User01\MyDocuments\secondfile.pptx	File	1.6

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:

FullPath	Type	No_of_files
Computer\User01\MyDocuments\	Folder	1500
Computer\User01\Pictures\	Folder	20
Computer\User01\Desktop\	Folder	14
Computer\User01\Downloads\	Folder	10 000

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

Any help would be much appreciated, thanks!

CodePudding user response：

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];