0

Context:

I am looking to figure out how to create a combined sql statement that would sum the size and count the number of files in a particular volume path saved in the file column of a table. The path is an absolute path from the root volume all the way up to the file location. some volume paths have upwards of 400+ characters includes white space and special characters. I an looking to get a list size and file count based on the list of folders in the target location. I am able to extract the file list or sum & count all files in the target folder but not for each of the sub folder in one more level deeper. I was working with a flat file that had this information in it per line and I used php to import it into the MariaDB so we can run queries and report on the given information.

select distinct left(replace(file, '/volume/path/to/target/folder/', '' ),locate("/",replace(file, '/volume/path/to/target/folder/', '' ))) from FileList

Output:

These/
are/
the/
folders/
are/
looking/
for/

whey trying to group by:

select left(replace(file, '/volume/path/to/target/folder/', '' ),locate("/",replace(file, '/volume/path/to/target/folder/', '' ))) as 'folder', sum(realsize), count(file)  from FileList where file like '/volume/path/to/target/folder%' group by 'folder'

output:

folder, sum(realsize), count(file)
<blank>, 127534970, 2340239024

Trying a subquery: 

select 
    left(replace(file, '/volume/path/to/target/folder/', '' ),locate("/",replace(file, '/volume/path/to/target/folder/', '' ))) as 'folder'
    count(file),
    sum(realsize)
From
    FileList
Where
    folder in (select distinct left(replace(file, '/volume/path/to/target/folder/', '' ),locate("/",replace(file, '/volume/path/to/target/folder/', '' ))) from FileList)

output:

folder, sum, count
*NULL*,127534970,*NULL*

Question:

What am doing wrong with combining to get the output I am looking for?

expected output:

(random number to simulate output)
folder, sum, count
These/, 1245, 234
are/  , 2345, 345
the/  , 8675, 456
folders/, 345, 34
are/  , 9876, 123
looking/, 456, 2
for/, 2346890, 987
thebtm
  • 215
  • 9
  • 14
  • Don't put quotes around the column name in `group by folder`. That's why you're getting just one row of results -- everything is being grouped together by that literal value. – Barmar May 11 '17 at 22:45
  • Your solution fixed the issue. I don't know if my question is a duplicate question of the one you linked but but it has the same answer. – thebtm May 11 '17 at 22:54
  • That's my general criteria for marking as duplicates -- if the solutions are essentially the same. It's rare that questions are exactly the same, but they're similar enough. – Barmar May 11 '17 at 22:57
  • Fair enough about the explanation. – thebtm May 11 '17 at 22:59

0 Answers0