Correlated Query Summing Question

[flamingo]

I have a query that works just fine, but because I need to use it in Business Objects and I have to use a prompt for the date, I need the initial query for the count as a correlated subquery in the from clause, not in the select statement. I've figured out what I'd like to have, however, I only get one row back (and it is correct!) I need to get the results that the initial query gives me, which is about 10 rows. Is there any way I can do this? (I'm using MS SQL server)

Thanks!!
Susan
(Both queries are below)

Query I know works and am currently using:

select distinct
r.IndividualFirstResolve,
(select sum(n.NumberOfCalls)
from dbo.SolutionCenterTelephone n
where r.IndividualFirstResolve=n.ReceiveCallName
AND substring((convert(char(6),n.calldate,112)),1,7)=
(select max(substring((convert(char(6),k.calldate,112)),1, 7))
from dbo.SolutionCenterTelephone k))
from dbo.HelpDeskTicket r
where r.IndividualFirstResolve in (select distinct e.ReceiveCallName
from dbo.SolutionCenterTelephone e
where department='HelpDesk')


What I'd like to have:

select distinct r.IndividualFirstResolve, ticketId as newcount
from dbo.HelpDeskTicket r, dbo.SolutionCenterTelephone e
where
r.IndividualFirstResolve in (select e.ReceiveCallName
from dbo.SolutionCenterTelephone e
where department='HelpDesk')
AND ticketId in (select sum(n.NumberOfCalls)
from dbo.SolutionCenterTelephone n
where IndividualFirstResolve=n.ReceiveCallName
AND substring((convert(char(6),n.calldate,112)),1,7)=
(select max(substring((convert(char(6),k.calldate,112)),1, 7))
from dbo.SolutionCenterTelephone k))