473,320 Members | 1,977 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.
Oct 3 '08 #1
4 3747
code green
1,726 Expert 1GB
SUM and COUNT are different.
COUNT returns the number of rows
SUM adds all the values of a field.
Oct 3 '08 #2
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
Oct 6 '08 #3
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.
Oct 6 '08 #4
code green
1,726 Expert 1GB
I missed your post yesterday but well done on resolving the problem.
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
Oct 7 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Matt | last post by:
In ASP page, if we want to retrive the number of rows in database: should we use recordset object? Since this is not update, insert, delete, or select statement sqlStmt = "SELECT COUNT(*) from...
2
by: bsder | last post by:
Hi, If I want to select a result that based on the value of count (eg. count(*) > 5), how can I write a sql to do that? eg. select count(*)>5 from Flight where OperationType = "Departure"...
2
by: brandon | last post by:
I've followed the instructions here...
12
by: Clifford Stern | last post by:
In a function that returns two values, how do you read them? Consider the following function: int addsub(int x,int y) {int a,b; a=x+y; b=x-y; return(a,b);} trying to read the results, for...
5
by: Homer Simpson | last post by:
Hi All, I'm trying to write a method where I pass three arguments and the method returns six values. All the values will be doubles. First, is it possible to get multiple values returned by a...
1
by: satish mullapudi | last post by:
Hi, I am using DB2 v8.2 & jdk 1.4. 1. I have a java program which returns an array which contains 1 to 5 numbers. The code: public class ArrayTest { public static final int ARRAY_SIZE = 5;...
2
by: PraveenAnekalmat | last post by:
Hi everyone String totalDbString = "ABC,DEF,GHI"; String queryString = "SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( ? )"; PreparedStatement ps =...
4
by: rocksters | last post by:
HI , Can u tell how to add the two values returned by two different fuctions in different cases of switches????
5
by: cmt | last post by:
I have an ASP report that returns values from a SQL database and formats the data in an HTML table. I am trying to figure out a good way of using CSS to highlight the table row that contains the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.