SQL query to get output

Question

I have two queries.

First query:

select files.originalfilename
from files
inner join digitalfiles on files.fileid = digitalfiles.fileid
where digitalfiles.path in (
 select *
 from Staple
 )

This will get the orginialfilename with extensions. Like below:

originalfilename

abc.doc

cool.bmp

1342.png

at.docx

Now the second query:

select ft.Extension
 ,COUNT(f.OriginalFileName) as [Count]
 ,sum(df.Length) / 1024 as [Size KB]
 ,ft.FileTypeId
 ,cast(count(f.OriginalFileName) * 100.0 / sum(count(*)) over () as decimal(18, 6)) as [Pct]
from Files f
inner join DigitalFiles df on f.FileId = df.FileId
left join FileTypes ft on df.FileTypeId = ft.FileTypeId
where f.Deleted = 1
group by ft.Extension
 ,ft.FileTypeId

This query will give me output as: Extension, Count, Size KB, File ID, Percentage

I want to use the result of first query in second query and get the result.

• Venkata Kalyan: I would like to make use of first query result and get the new output as below Extension Count Size KB FileTypeId Pct pdf 2 8556 4 20.000000 docx 1 200 2 13.78 png 5 789999 3 56.6532 means to take only the extensions of file names and calculate the number, count, % etc.. as above. hope you understand.

Answer 1

I might be misunderstanding the question, but assuming that I do understand it, it seems that you could fairly easily take the where clause from the first

where digitalfiles.path in (
 select *
 from Staple
 )

And make it a part of the second query.

There are a number of ways you could do this (CTE, derived table, etc...) but it seems like just adding that to the where clause is probably as easy as anything.

select ft.Extension
 ,COUNT(f.OriginalFileName) as [Count]
 ,sum(df.Length) / 1024 as [Size KB]
 ,ft.FileTypeId
 ,cast(count(f.OriginalFileName) * 100.0 / sum(count(*)) over () as decimal(18, 6)) as [Pct]
from Files f
inner join DigitalFiles df on f.FileId = df.FileId
left join FileTypes ft on df.FileTypeId = ft.FileTypeId
where f.Deleted = 1
AND df.path in (select * from Staple)
group by ft.Extension
 ,ft.FileTypeId;