How do i SUM up values returned by TOP 5 and COUNT statement?

I want to be able to SUM the values returned using the TOP 5 and COUNt statements. So far I've tried from many angles and can't get it to work, very frustrating! Here is my code:

SELECT TOP 5 WITH TIES eworkCalls.Category,eworkCalls.SubCategory,
ClStandingdata.tblRoutes_AREA AS Route, ClStandingdata.[DAY] AS BinDay,
eworkcalls.sla as SLA,
COUNT(*) AS [Total Closed Calls]
FROM cleansing, eworkcalls LEFT OUTER JOIN clstandingdata
ON clstandingdata.mad_UPRN=eworkcalls.UPRN
WHERE (cleansing.efolderid = eworkcalls.subfolderid) AND (eworkCalls.SubStatus = 'Complete')
AND (eworkCalls.[Date] >='2002-01-02') AND (eworkCalls.[Date] < '2008-08-29')
AND (cleansing.txtJobCompleted = 'Yes')
AND (eworkCalls.Category = 'Waste')
AND (clstandingdata.tblRoutes_AREA = 'Route 1')
AND (clstandingdata.[DAY] = 'Thursday')
GROUP BY ClStandingdata.tblRoutes_AREA,ClStandingdata.[DAY],
eworkCalls.Category,eworkCalls.SubCategory,sla
ORDER BY [Total Closed Calls]DESC

I get values for 5 rows as: 459, 336, 185, 184, 58
The SUM total should be 1222 but I keep getting 1534, which is the number of rows returned, if I remove the TOP 5 statement.

Can anybody shed some light on how I can sum up the top 5 rows values. Thanking you in advance.

re: How do i SUM up values returned by TOP 5 and COUNT statement?

SUM and COUNT are different.
COUNT returns the number of rows
SUM adds all the values of a field.

re: How do i SUM up values returned by TOP 5 and COUNT statement?

When I posted this I was in a bit of a rush, apologise here. To explain a bit more I have a select statement in PHP which calls a User Defined Function. The main select code is in the UDF which does the COUNT which I have to show on the web page table display, this is required, then the PHP page has the SELECT TOP 5 SUM part. The UDF returns a table of the main select results and the PHP should show the TOP 5 results of the table and SUM only the top 5. Here is the full code structure:

PHP with values being passed to the UDF:
SELECT TOP 5 Category, SubCategory, Route, Binday, SLA, SUM(CASE FLAG WHEN '1' THEN totalrecords ELSE 0 END) AS [Total Closed Calls] FROM dbo.fn_cl_eworkcallscomptop5('2002-01-02','2008-08-30',NULL,NULL,'Route 1','Thursday','Waste')GROUP BY Category, SubCategory, Route, Binday, SLA ORDER BY [Total Closed Calls] DESC

Here is the UDF Code:
SELECT eworkcalls.Category, eworkcalls.subcategory, clstandingdata.tblRoutes_AREA as Route, clstandingdata.[DAY] as Binday, eworkcalls.sla as SLA,
SUM(eworkcalls.withintarget) as withintarget,
SUM(eworkcalls.outwithtarget) as outwithtarget,
SUM(case WHEN eworkcalls.Escalation1 IS NULL THEN 0 ELSE 1 END) as Escalation1, SUM(case WHEN eworkcalls.Escalation2 IS NULL THEN 0 ELSE 1 END) as Escalation2, count(*) as totalrecords, '1' as flag
FROM cleansing, eworkcalls LEFT OUTER JOIN clstandingdata
ON clstandingdata.mad_UPRN=eworkcalls.UPRN
WHERE (cleansing.efolderid= eworkcalls.subfolderid) AND (eworkCalls.SubStatus = 'Complete')
AND (eworkCalls.[Date] >='2002-01-02') AND (eworkCalls.[Date] <'2008-08-30')
AND (cleansing.txtJobCompleted = 'Yes')
AND (eworkCalls.Category = 'Waste')
AND (clstandingdata.tblRoutes_AREA = 'Route 1')
AND (ClStandingdata.[DAY] = 'Thursday')
GROUP BY Category, subcategory, tblRoutes_AREA, clstandingdata.[DAY], sla, withintarget, outwithtarget, Escalation1,Escalation2
ORDER BY totalrecords DESC

I'm new to PHP and indepth SQL, so this is a very steep learning curve.

Thanks in advance

re: How do i SUM up values returned by TOP 5 and COUNT statement?

Well, with perseverance I've managed to get it working. I placed the SELECT TOP 5 in the UDF so that the returned table results would only hold the TOP 5 rows returned. Then I removed the TOP 5 from the calling PHP. At this point I was still getting the wrong sum. With a bit more investigation, when I removed the withintarget, outwithtarget and escalation1 & escalation2 from the GROUPBY clause, the SUM worked.

Still to get a handle on why Grouping can cause so many problems.

Thanks for your help Code Green, it was appreciated.

re: How do i SUM up values returned by TOP 5 and COUNT statement?

I missed your post yesterday but well done on resolving the problem.
Not quite sure where this is causing you a problem.
But it is a case of understanding what the GROUP BY clause does.
I think of it as a DISTINCT working on a set of fields with the fields placed in a certain order.
In MySql GROUP BY can be used instead of DISTINCT
but MsSQl won't let you aggregate just one field.
They all must be aggregated so it means the GROUP BY clause lists every field being selected