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.IndividualFir stResolve,
(select sum(n.NumberOfC alls)
from dbo.SolutionCen terTelephone n
where r.IndividualFir stResolve=n.Rec eiveCallName
AND substring((conv ert(char(6),n.c alldate,112)),1 ,7)=
(select max(substring(( convert(char(6) ,k.calldate,112 )),1,7))
from dbo.SolutionCen terTelephone k))
from dbo.HelpDeskTic ket r
where r.IndividualFir stResolve in (select distinct e.ReceiveCallNa me
from dbo.SolutionCen terTelephone e
where department='Hel pDesk')


What I'd like to have:

select distinct r.IndividualFir stResolve, ticketId as newcount
from dbo.HelpDeskTic ket r, dbo.SolutionCen terTelephone e
where
r.IndividualFir stResolve in (select e.ReceiveCallNa me
from dbo.SolutionCen terTelephone e
where department='Hel pDesk')
AND ticketId in (select sum(n.NumberOfC alls)
from dbo.SolutionCen terTelephone n
where IndividualFirst Resolve=n.Recei veCallName
AND substring((conv ert(char(6),n.c alldate,112)),1 ,7)=
(select max(substring(( convert(char(6) ,k.calldate,112 )),1,7))
from dbo.SolutionCen terTelephone k))