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.
Best Answer
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
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.