How to sum returned records?

# Thread: How to sum returned records?

1. Tory Guest

## How to sum returned records?

Looked in beginner level questions, but didn&#039;t find answer...How do I use a SUM function in ASP to total each column of returned records? Thanks

2. Senior Member
Join Date
Dec 1969
Posts
590

## RE: How to sum returned records?

It is not clear what you do,but look in help about "compute" and "compute by" at least in sql server..

3. jamar Guest

## RE: How to sum returned records?

yeah, it&#039;s more efficient to sum() in your sql statement, otherwise you have to loop through the rs in asp. do this by using a compute clause...

4. Tory Guest

## RE: How to sum returned records?

Thanks - Had thought I could do it in ASP. Tory

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## In ASP code? Easy enough:

&#060;%<BR>...<BR>sumSalary = 0<BR>sumFICA = 0<BR>sumFIT = 0<BR>Do Until RS.EOF<BR>&nbsp; &nbsp; ... display stuff ...<BR>&nbsp; &nbsp; sumSalary = sumSalary + RS("Salary")<BR>&nbsp; &nbsp; sumFICA = sumFICA + RS("FICA")<BR>&nbsp; &nbsp; sumFIT = sumFIT + RS("FIT")<BR>&nbsp; &nbsp; ...<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>...<BR>%&#062;<BR><BR>As others have advised you, you can use the SQL SUM(columnname) aggregate to total a column, but you can&#039;t return the SUM in the same recordset where you return the individual values, so you end up doing *two* queries...one for the data, one for the sum.<BR><BR>Almost surely, the tiny bit of added code in VBS is more efficient.<BR><BR>

6. Tory Guest

## RE: In ASP code? Easy enough:

Bill - I tried your code, but it returned a FALSE value for each of the five variables I was trying to sum. Something obvious i&#039;m missing? Code below<BR><BR>SUMsingleunit = 0<BR>SUMsinglevalue = 0<BR>SUMmultibldgs = 0<BR>SUMmultiunits = 0<BR>SUMmultivalue = 0<BR>Do while NOT objRS.EOF<BR> RowCounter = RowCounter + 1<BR> Response.Write "&#060;TR&#062;&#060;TD&#062;" & RowCounter & ".&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;" & objRS("coname") & " county&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;" & objRS("place") & " &#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatNumber(objRS("singleunit"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatCurrency(objRS("singlevalue"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatNumber(objRS("multibldgs"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatNumber(objRS("multiunits"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatCurrency(objRS("multivalue"),0) & "&#060;/div&#062;&#060;/TD&#062;&#060;/TR&#062;"<BR> Response.Write "&#060;TR&#062;&#060;TD&#062;" & SUMsingleunit = SUMsingleunit + objRS("singleunit") & "&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;" & SUMsinglevalue = SUMsinglevalue + objRS("singlevalue") & "&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;" & SUMmultibldgs = SUMmultibldgs + objRS("multibldgs") & "&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;" & SUMmultiunits = SUMmultiunits + objRS("multiunits") & "&#060;/TD&#062;"<BR> Response.Write "&#060;TD&#062;" & SUMmultivalue = SUMmultivalue + objRS("multivalue") & "&#060;/TD&#062;&#060;/TR&#062;"<BR> objRS.MoveNext<BR> Loop<BR> objRS.Close

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## A true newbie? Sorry...

Sorry about that...maybe I should have been more explicit.<BR><BR>*NONE* of the code I was showing should have been used in Response.Write statements.<BR><BR>Those are *ARITHMETIC* statements, for doing calculations, not for outputting to the screen.<BR><BR>*AFTER* you got all the way through the loop of records, *THEN* you would Response.Write the final sums.<BR><BR>If this is beyond what you understand at this point, the go ahead and do two SQL queries instead. <BR><BR>Oh, what the heck: I&#039;ll take a whack at answering (untested!), but you really need to get a good book and learn more about programming. At a minimum, you should understand what I have done here. If you don&#039;t, then don&#039;t try to use it. Because if it has bugs, how will you fix it?<BR><BR>&#060;%<BR>...<BR>SUMsingleunit = 0<BR>SUMsinglevalue = 0<BR>SUMmultibldgs = 0<BR>SUMmultiunits = 0<BR>SUMmultivalue = 0<BR>Do while NOT objRS.EOF<BR>&nbsp; &nbsp; RowCounter = RowCounter + 1<BR>&nbsp; &nbsp; Response.Write "&#060;TR&#062;&#060;TD&#062;" & RowCounter & ".&#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;" & objRS("coname") & " county&#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;" & objRS("place") & " &#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatNumber(objRS("singleunit"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatCurrency(objRS("singlevalue"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatNumber(objRS("multibldgs"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatNumber(objRS("multiunits"),0) & "&#060;/div&#062;&#060;/TD&#062;"<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;&#060;div align=""right""&#062;" & FormatCurrency(objRS("multivalue"),0) & "&#060;/div&#062;&#060;/TD&#062;&#060;/TR&#062;"<BR>&nbsp; &nbsp; SUMsingleunit = SUMsingleunit + objRS("singleunit")<BR>&nbsp; &nbsp; SUMsinglevalue = SUMsinglevalue + objRS("singlevalue")<BR>&nbsp; &nbsp; SUMmultibldgs = SUMmultibldgs + objRS("multibldgs")<BR>&nbsp; &nbsp; SUMmultiunits = SUMmultiunits + objRS("multiunits")<BR>&nbsp; &nbsp; SUMmultivalue = SUMmultivalue + objRS("multivalue")<BR>objRS.MoveNext<BR>Loop<BR>o bjRS.Close<BR><BR>Response.Write "&#060;TR&#062;&#060;TD&#062;" & SUMsingleunit & "&#060;/TD&#062;"<BR>Response.Write "&#060;TD&#062;" & SUMsinglevalue & "&#060;/TD&#062;"<BR>Response.Write "&#060;TD&#062;" & SUMmultibldgs & "&#060;/TD&#062;"<BR>Response.Write "&#060;TD&#062;" & SUMmultiunits & "&#060;/TD&#062;"<BR>Response.Write "&#060;TD&#062;" & SUMmultivalue & "&#060;/TD&#062;&#060;/TR&#062;"<BR>...<BR>%&#062;<BR><BR>You&#039;ll have to pretty it up with FormatCurrency, et al, as needed.<BR><BR><BR>

8. tory Guest

## RE: A true newbie? Sorry...

Thanks. Yes, a true newbie - but not for long. Tory

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•