Query number of files in state?

Hi guys, does anyone have an SQL Query for Number of Files sitting in each State of their Workflow?

I have this query:

select T.Name + ' (' + W.Name + ')' as [Transition(Workflow Name)], Count(1) as [Count] from TransitionHistory TH

inner join Transitions T on TH.TransitionID = T.TransitionID

inner join workflows W on W.WorkflowID = T.WorkflowID

where W.WorkflowID > 1

group by W.Name, T.Name

order by 2 Desc

and it will count how many files have gone through a transition, but I'm looking for something that will interrogate the Projects table to count # of files eg.. S.Name AS 'Status Name' as an int

So it would look like:

StateCount
In work400
Checking75
Released12000

Any tips or advice on how to achieve this would be much appreciated

Thanks!

SolidworksSolidworks Pdm/enterprise Pdm