Solved

adding numbers in a database

Posted on 2004-08-13
4
135 Views
Last Modified: 2013-12-24
hello all,

I have a table with about 15+ records.
There is a number field and I want to add all the numbers together.  How can I do that?
Thanks.
0
Comment
Question by:Isaac
  • 2
4 Comments
 
LVL 5

Author Comment

by:Isaac
ID: 11798624
This is the code I have:

<CFQUERY NAME="view" datasource="A99">
      SELECT * FROM tblCount
</CFQUERY>

<cfloop query="view">
<cfset cnt = 0>
<CFSET cnt = cnt + #numb#>
</cfloop>
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 400 total points
ID: 11798725
Actually, you can have it in this manner :

<CFQUERY NAME="view" datasource="A99">
  SELECT sum(number_field) as totalno
  FROM tblCount
</CFQUERY>

<cfoutput>#view.totalno#</cfoutput>

The result will give you the sum of all number within that table.

Regards!
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 11798743
Then by the way, just to correct your code.

You can have it in this way:

<cfparam name = "cnt" default = "0">

<CFQUERY NAME="view" datasource="A99">
  SELECT * FROM tblCount
</CFQUERY>

<cfloop query="view">
  <CFSET cnt = cnt + numb>
</cfloop>

<cfoutput>#cnt#</cfoutput>

Then, the result is still the same with the code above.

Best wishes!
eNTRANCE2002 :-)
0
 
LVL 6

Assisted Solution

by:reitzen
reitzen earned 100 total points
ID: 11798988
entrance2002 is absolutely correct in his comment.  There are usually several ways to write a section of code and still get the same results.  That being said, instead of rewriting your code, my comment is included to help you understand why your code doesn't work.  So, you can become more familiar with the CF language.

First, your query is okay.  However, for performance reasons, you want to stay away from using the wildcard "*".  Listing all of your field names will give you better performance (on tables with lots of columns) and you'll have a visible list of what fields your query will be returning.

Second, you will never get an accurate value in the "cnt" variable.  Why?  Because you are setting its value back to zero with every loop.  Move the line "<cfset cnt = 0>" above the <cfloop query="view"> and you'll see the results of the "cnt" variable returning the correct value.

Third, when you are referring to variables within a CF tag, you don't need to use the pound signs unless you are surrounding the variable in quotes (there are a couple of exceptions, but this is typically the rule).
Example:
Right  =  <cfset cnt = cnt + numb>
Wrong = <cfset cnt = cnt + #numb#>

Right = <cfset cnt = cnt + "#numb#">
Wrong = <cfset cnt = cnt + "numb">  (CF will look at "numb" as a string)

HTH
Rob
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

839 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