Urgent help needed fo counts and sums between 2 tables

[eakinpeter]

I would really appreciate any help with guys guys as I'm nearing a deadline and I need this donw before I can
do other stuff.

I have 2 tables which show the details of files on 3 different file servers,
the tables are laid out below with an example from each:


file table

FILE_NAME, FULL_PATH, EXTENSION, SIZE
dirsize.exe u:\dirsize.exe .EXE 294912

dir table

DIR_NAME, DIR_PATH
AgutterPA u:\Data\User\AgutterPA


There are over 1 million records in the file table and about 400,000 records in the dir table.
I need to do the following using an sql query i would really appreciate it if someone could
give me a possible solution.

I need a count of all files in the file table that belong to each directory in
the dir table. So for each directory in the dir table how many files have a path
beginning with the same path as the dir path (if the file
path name has the substring dir path name then the file is held in that dir)

I also need to get the sum of all the file sizes in each directory so this would
be the same as the first example but the sum of each file in the directory would need
to be tallyed.

So at the end of the query i would need, 2 seperate queries would be ok if needed.

DIR_NAME COUNT_OF_FILES TOTAL_FILE_SIZE

Thanks
Peter

 

[lynchg]

Try this:

Select D.Dir_Name, Count(F.File_Name) as File_Count, Sum(F.Size) as Dir_Size
From Dir as D, File as F
Where D.Dir_Path = Left(F.Full_Path, Len(D.Dir_Path))
Group By D.Dir_Name

 

[PHV]

Something like this ?
SELECT D.DIR_NAME, Count(*) AS COUNT_OF_FILES, Sum(F.SIZE) AS TOTAL_FILE_SIZE
FROM dir_table AS D, file_table AS F
WHERE F.FULL_PATH = D.DIR_PATH & '\' & F.FILE_NAME
GROUP BY D.DIR_NAME

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eakinpeter]

Yes that's it guys, absolute genious's I tell ya.

Thanks a lot, i really appreciate it.

Peter