Query doesn't seem to SUM correctly

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.

RobAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jonnygo55Commented:
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
RobAAuthor Commented:
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
jonnygo55Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

RobAAuthor Commented:
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
jonnygo55Commented:
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
proceptCommented:
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
RobAAuthor Commented:
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
iamariCommented:
your Grade columns MUST be of type integer
0
proceptCommented:
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
RobAAuthor Commented:
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
mrichmonCommented:
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
RobAAuthor Commented:
mrichmon-

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

0
mrichmonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.