CODEWITHSUNDEEP
sql-server
user70884
user70884

Reputation: 27

SQL Server cumulative grouping

I have a table that has list of files and folders imported from a csv file. The table has as columns mainly FullName (path to the file or folder) and FileExtension.

I am able get a list of all folders and corresponding number of files in those folders using the below query

SELECT
    SUBSTRING(FullName, 0, LEN(FullName) - CHARINDEX('\', REVERSE(FullName)) + 1) AS RootFolder, 
    COUNT(*) AS FileCount
FROM
    FileDump 
WHERE
    NULLIF(FileExtension, '') IS NOT NULL
GROUP BY
    SUBSTRING(FullName, 0, LEN(FullName) - CHARINDEX('\', REVERSE(FullName)) + 1)
ORDER BY
    FileCount DESC

However what I would like to get is if the folder has subfolders and those subfolders have files, then the filecount for any folder should also include all those files underneath

Sample data for FullName

C:\Folder1
C:\Folder1\File1.xlsx
C:\Folder1\File2.xlsx
C:\Folder1\Folder2
C:\Folder1\Folder2\file3.xlsx

Expected result

C:\Folder1  - 3
C:\Folder1\Folder2 - 1

Upvotes: 1

Views: 55

Answers (1)

LukStorms
LukStorms

Reputation: 29647

One way is to start from the folders.
Which are those that don't have an extension.

Then left joins the files and group on the folders to count the files. 

SELECT 
 [folder].FullName AS RootFolder, 
 COUNT([file].FullName) AS FileCount
FROM FileDump AS [folder]
LEFT JOIN FileDump AS [file] 
  ON NULLIF([file].FileExtension, '') IS NOT NULL 
 AND [file].FullName LIKE CONCAT([folder].FullName,'\%')
WHERE NULLIF([folder].FileExtension, '') IS NULL
GROUP BY [folder].FullName
ORDER BY RootFolder;

Result:

RootFolder          FileCount
------------------  ---------
C:\Folder1          3
C:\Folder1\Folder2  1

Upvotes: 1

Related Questions