Solved

adding numbers in a database

Posted on 2004-08-13
4
132 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now