Connecting Tech Pros Worldwide Help | Site Map

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

 
LinkBack Thread Tools Search this Thread
  #1  
Old October 3rd, 2008, 02:48 PM
Newbie
 
Join Date: Oct 2008
Posts: 3
Default 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.
Reply
  #2  
Old October 3rd, 2008, 04:00 PM
code green's Avatar
Expert
 
Join Date: Mar 2007
Posts: 963
Default

SUM and COUNT are different.
COUNT returns the number of rows
SUM adds all the values of a field.
Reply
  #3  
Old October 6th, 2008, 08:36 AM
Newbie
 
Join Date: Oct 2008
Posts: 3
Default

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
Reply
  #4  
Old October 6th, 2008, 11:32 AM
Newbie
 
Join Date: Oct 2008
Posts: 3
Default

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.
Reply
  #5  
Old October 7th, 2008, 08:09 AM
code green's Avatar
Expert
 
Join Date: Mar 2007
Posts: 963
Default

I missed your post yesterday but well done on resolving the problem.
Quote:
Still to get a handle on why Grouping can cause so many problems
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
Reply
Reply

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search


Popular Articles

What is Bytes?

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 220,989 network members.