add colum fields retrieved for a total?

Results 1 to 3 of 3

Thread: add colum fields retrieved for a total?

  1. #1
    genic Guest

    Default add colum fields retrieved for a total?

    ok, here is what i have. i have a db field called hours.<BR>i pull all the fields to display them. &#060;%=rs("hours")%&#062; problemo. what i would like to do is take ALL the values for that field within the recordset...add them up..and display them. i have looked and looked and i cant find anything. help?

  2. #2
    genic Guest

    Default i forgot

    if one field has the value of 5 ..another might have 2.5 ...the next might have 8. the total should come out to 15.5<BR><BR>make sense?

  3. #3
    Join Date
    Dec 1969

    Default Two ways, one much better...

    There are generally two ways to do what you need: <BR><BR>(1) Use a separate SQL query to get the sum.<BR><BR>&#060;%<BR>Set rsTotal = yourConnection.Execute("SELECT Sum(hours) AS TotalHours FROM table WHERE ...")<BR>TotalHours = rsTotal("TotalHours")<BR>%&#062;<BR><BR>But that&#039s expensive in terms of time and performance, isn&#039t it? You&#039d rather get the total at the same time you get all the rows, right? Problem is, SQL won&#039t let you do that. You can&#039t use an "aggregate" function (such as Sum) at the same time you get detailed record information.<BR><BR>So...<BR><BR>(2) You use a little VBScript programming.<BR><BR>&#060;%<BR>Set RS = yourConnection("SELECT whoever,whatever,hours FROM table ...")<BR><BR>TotalHours = 0.0<BR><BR>Do Until RS.EOF<BR>&nbsp; &nbsp; TotalHours = TotalHours + CDbl(RS("hours"))<BR>&nbsp; &nbsp; ... display info about current record.<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>...<BR>Response.Write "Total hours is " & FormatNumber( TotalHours, 2 )<BR>...<BR>%&#062;<BR><BR>This is what programming is all about, incidentally: Using your own methods to solve problems. Often, there is no "pre-canned" solution for what you are looking for.<BR><BR>

Posting Permissions

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