Grouping file paths in a Microsoft SQL database

Question

I have a table with a list of folders that looks like this:

Path                    Size
C:\ParentFolder\A       123
C:\ParentFolder\A\B     442434
C:\ParentFolder\A\B\C   13413412
C:\ParentFolder\D       2422341234
C:\ParentFolder\D\E     3342
C:\ParentFolder\D\E\F   2
C:\ParentFolder\D\E\G   2
...

I'm looking for some combination of SUM, GROUP BY, and PATINDEX/LTRIM/SUBSTRING/etc. which would give me back this:

Path                    SumSize
C:\ParentFolder\A       13855969
C:\ParentFolder\D       2422344580
...

C:\ParentFolder is a known prefix, but A,D,etc. are variable folder names. Do I need to write a function to accomplish that or can I use some combination of string functions?

One level down? Yes, I want to summarize A and everything under A. D and everything under D and nothing else. The trick is I don't know what A and D are until runtime. Version? SQL 2008R2 — Will
– Will, Commented Jun 7, 2012 at 14:26

D'Arcy Rittich · Accepted Answer · 2012-06-07 18:25:45Z

1

select r.Path, sum(Size) as SumSize
from MyTable m
inner join (
    select Path
    from MyTable 
    where charindex('\', Path, len('C:\ParentFolder\') + 1) = 0 
) r on charindex(r.Path, m.Path, 0) = 1
group by r.Path

SQL Fiddle example here

edited Jun 7, 2012 at 18:25

answered Jun 7, 2012 at 14:13

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philip Kelley · Accepted Answer · 2012-06-07 14:15:29Z

0

Starting with your testing set,

CREATE TABLE #MyTable (Folder varchar(100) not null, Size bigint not null)

INSERT #MyTable values
  ('C:\ParentFolder\A'     ,  123)
 ,('C:\ParentFolder\A\B'   ,  442434)
 ,('C:\ParentFolder\A\B\C' ,  13413412)
 ,('C:\ParentFolder\D'     ,  2422341234)
 ,('C:\ParentFolder\D\E'   ,  3342)
 ,('C:\ParentFolder\D\E\F' ,  2)
 ,('C:\ParentFolder\D\E\G' ,  2)

you'd first determine what folders you want to summarize. I do so here by loading them into a temp table:

DECLARE @Targets table (Folder varchar(100) not null)
INSERT @Targets values
  ('C:\ParentFolder\A')
 ,('C:\ParentFolder\D')

From here it's easy, using the like clause:

SELECT ta.Folder, sum(Size) TotalSize
 from @Targets ta
  left outer join #MyTable mt
   on mt.Folder like ta.Folder + '%'
 group by ta.Folder

Complications may ensue if your folders contain reserved characters used by the like clause: % _ ] [ and a few others.

answered Jun 7, 2012 at 14:15

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

2 Comments

anouar.bagari Over a year ago

I think it will give worg result due to the duplication when using like operator

Will Over a year ago

This solution was the cleanest. The only adjustment I had to make was change INSERT @Targets values ('C:\ParentFolder\A') ,('C:\ParentFolder\D') Into a dynamic query that would discover the folders at runtime.

Alex K. · Accepted Answer · 2012-06-07 14:40:06Z

0

Assuming that there is always an entry for the highest level dir (i.e if there is a c:\xxx\yyy\zzz there will always be a c:\xxx\yyy how about

;with roots (root) as (
     select distinct
        path + '\' 
     from 
        thetable
     where 
        --only include paths with 2 x \
        len(path) - 2 = len(replace(path, '\', '')) 
)
select
    roots.root,
    sum(thetable.size)
from 
    roots
inner join 
    thetable on left(thetable.path + '\', len(roots.root)) = roots.root 
group by
    roots.root

answered Jun 7, 2012 at 14:40

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Comments

Dibstar · Accepted Answer · 2012-06-07 14:41:57Z

0

--If the folder name is always one character

select 
LEFT(folder,CHARINDEX('r\',folder)+2) as folder_group
,SUM(size) as sumsize
from #mytable
GROUP BY
LEFT(folder,CHARINDEX('r\',folder)+2)

--If the folder name has a variable length

select 
CASE WHEN CHARINDEX('\',folder,CHARINDEX('\',folder,CHARINDEX('\',folder)+1)+1) = 0 THEN folder
    ELSE LEFT(folder,CHARINDEX('\',folder,CHARINDEX('\',folder,CHARINDEX('\',folder)+1)+1) -1) END AS folder_group
,SUM(size) as sumsize
from #mytable
GROUP BY
CASE WHEN CHARINDEX('\',folder,CHARINDEX('\',folder,CHARINDEX('\',folder)+1)+1) = 0 THEN folder
    ELSE LEFT(folder,CHARINDEX('\',folder,CHARINDEX('\',folder,CHARINDEX('\',folder)+1)+1) -1) END

answered Jun 7, 2012 at 14:41

Dibstar

2,3742 gold badges25 silver badges38 bronze badges

Comments

anouar.bagari · Accepted Answer · 2012-06-07 14:59:52Z

0

 select path, (select sum(Size) 
               from Paths p2 where p2.Path like p1.Path+'%') as total
 from Paths p1
 where charIndex('\',Path, len('C:\ParentFolder\')+1) = 0

edited Jun 7, 2012 at 14:59

answered Jun 7, 2012 at 14:41

anouar.bagari

2,10419 silver badges30 bronze badges

1 Comment

D'Arcy Rittich Over a year ago

You are making the assumption that immediate children names will only be one character long, "but A,D,etc. are variable folder names."

Collectives™ on Stack Overflow

Grouping file paths in a Microsoft SQL database

5 Answers 5

Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related