[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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.

0
RobA
Asked:
RobA
  • 5
  • 3
  • 2
  • +2
1 Solution
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now