?
Solved

Query doesn't seem to SUM correctly

Posted on 2003-11-14
16
Medium Priority
?
233 Views
Last Modified: 2013-12-24
For the following query:

<cfquery name="CkTotalRegistration" datasource="#DataSource#" username="#username#" password="#password#">
SELECT SUM(grade1+grade2+grade3+grade4+grade5+grade6+grade7+grade8+grade9+grade10+grade11+grade12+grade13+grade14+grade15+grade16+grade17) AS result_sum
FROM EdSales
 WHERE
dov BETWEEN #CreateODBCDate(Form.SDate)# AND #CreateODBCDate(Form.EDate)#
</cfquery>

Output via:

#NUMBERFORMAT (CkTotalRegistration.result_sum,'___')#

Shouldn't I get the total for all fields in the range specified?
If so, I'm doing something wrong because I get the wrong total.

0
Comment
Question by:RobA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
16 Comments
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9750680
you will get a sum of all the records of all the fields within that date range...

what total are you expecting to receive...?

perhaps you want only for a particular student?  if so you will have to add that to your where clause...
0
 

Author Comment

by:RobA
ID: 9750829
For each of the columns (grade1-grade17) there is a qty (0 or the number of students in that grade).
What I want is a total number of students from all the records returned in the date range (Sdate - Edate).
I seem to be getting about half the expected result currently


partial code follows:

<!--- Query for date range --->
</h3>
<cfquery name="CkTotalRegistration" datasource="#DataSource#" username="#username#" password="#password#">
SELECT SUM(grade1+grade2+grade3+grade4+grade5+grade6+grade7+grade8+grade9+grade10+grade11+grade12+grade13+grade14+grade15+grade16+grade17) AS result_sum
FROM EdSales
 WHERE
dov BETWEEN #CreateODBCDate(Form.SDate)# AND #CreateODBCDate(Form.EDate)#
</cfquery>

<cfquery name="Getdaterange" datasource="#DataSource#" username="#username#" password="#password#">
select *
from EdSales
WHERE
dov BETWEEN #CreateODBCDate(Form.SDate)# AND #CreateODBCDate(Form.EDate)#
ORDER BY DOV,OrgName
</cfquery>
<!--- If records not found show 'em this --->
<cfif Getdaterange.recordcount is "0">
  <strong>Sorry,
  no records found for date range.</strong>
  <p><font size="2" face="Arial, Helvetica, sans-serif"><a href="../bydate.cfm">Try
    another search</a> : <a href="../index.cfm">Return
    to main menu</a></font></p>
</cfif>
<!--- If records found show 'em this --->
<cfif Getdaterange.recordcount is NOT "0">
 
<!--- display record & ticket counts to user --->
<CFoutput><font size="2">There  are a total of <strong>#getdaterange.RecordCount#</strong> school(s) <strong>#NUMBERFORMAT (CkTotalRegistration.result_sum,'___')#</strong> student(s) registered for this date range.</font></CFoutput></font><font size="2"><CFoutput>
      </CFoutput>        
0
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9751031
perhaps its your date range...how are you supplying the date from the form?  day and month or just month? it mght not be including what you are expecting to the query....
can you supply the form.sDate and form.Edate before it goes into the CreateODBCDATE?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:RobA
ID: 9751098
this is(part of) the form the posts to the action page. Validated as date

<cfinput name="sdate" type="text" id="sdate" message="Please enter a correctly formatted start date (mm/dd/yyyy)" validate="date" required="yes">
            </font><font size="2">(mm/dd/yyyy)</font><br>
<cfinput name="edate" type="text" id="edate" message="Please enter a correctly formatted ending date (mm/dd/yyyy)" validate="date" required="yes">

It seems to be something in the actual query....
0
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9751286
so what is the field dov?  is it a datetime field in the database?

otherthan that I would just try outputting all of the data into a table and munually count up the numbers by row and by columns to see where the data is being left off...

<cfquery name="CkTotalRegistration" datasource="#DataSource#" username="#username#" password="#password#">
SELECT *
FROM EdSales
 WHERE
dov BETWEEN #CreateODBCDate(Form.SDate)# AND #CreateODBCDate(Form.EDate)#
</cfquery>

<cfoutput query="CkTotalRegistration">
     #grade1# #grade2# #grade3# #grade4# #grade5# #grade6# #grade7# #grade8# #grade9# #grade10# #grade11# #grade12# #grade13# #grade14# #grade15# #grade16# #grade17#<br>
</cfoutput>
0
 
LVL 4

Expert Comment

by:procept
ID: 9753279
Hi,

SUM() does a sum across records... to sum up fields, just use the '+' and assign the sum a name:

SELECT grade1+grade2+grade3+grade4+grade5+grade6+grade7+grade8+grade9+grade10+grade11+grade12+grade13+grade14+grade15+grade16+grade17 AS result_sum
FROM EdSales
 WHERE
dov BETWEEN #CreateODBCDate(Form.SDate)# AND #CreateODBCDate(Form.EDate)#

HTH,

Chris

0
 

Author Comment

by:RobA
ID: 9763293
Johnnygo-

When executing your query I get : Element GRADE1 is undefined


Chris-
I did as you indicate and it gives me a result of zero even for records that contain qty in the grade fields.

0
 
LVL 2

Expert Comment

by:iamari
ID: 9765244
your Grade columns MUST be of type integer
0
 
LVL 4

Expert Comment

by:procept
ID: 9769632
well, the fields must be of a numeric format... integer, float, single, double... whatever your DB offers, as long as it's numeric. ;-)

HTH,

Chris
0
 

Author Comment

by:RobA
ID: 9771650
fields are of a numeric format.
Now what I get is the sum for one record but not the total for all records returned from the query.

 
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 9781064
Are some of your fields NULL?  If not all fields have data then you may have problems using the + on the filed.
You can try a case in SQl to account for that.
0
 

Author Comment

by:RobA
ID: 9781253
mrichmon-

I do have NULL as a value in fields. How would I account for this in my CF query?

0
 
LVL 35

Accepted Solution

by:
mrichmon earned 200 total points
ID: 9781430
You would do something like this :

SELECT
CASE WHEN grade1 IS NULL THEN 0 ELSE grade1 END AS grade1 +
CASE WHEN grade2 IS NULL THEN 0 ELSE grade2 END AS grade2 +
CASE WHEN grade3 IS NULL THEN 0 ELSE grade3 END AS grade3 +
etc...
AS result_sum
FROM EdSales
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question