Adding up numeric values that are pulled from a db

Results 1 to 3 of 3

Thread: Adding up numeric values that are pulled from a db

  1. #1
    John F Guest

    Default Adding up numeric values that are pulled from a db

    Hi all,<BR><BR>I am writting a page that needs to pull specific records from a db(I am using a WHERE clause) and then add up the numeric values. I was trying use the SUM() function but only seems to work when you want to add up all the record in the db. Heres what I was up to:<BR>-----------------------------------------------------<BR>&#060;%<BR>vmarkets=Request.Form("markets")<BR ><BR>vmarket1=Replace(vmarkets,",","&#039 OR market=&#039")<BR><BR><BR>Set MyConn=Server.CreateObject("ADODB.Connection")<BR> MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("whatever.mdb") <BR><BR>&#039sSQL = "SELECT SUM(point) AS Total_Points FROM tblmarketinfo WHERE market=&#039" & vmarket1 & "&#039"<BR><BR>Set RS = MyConn.Execute(sSQL)<BR><BR>Total_Points = RS(0)<BR>Response.Write ("Total Points = " & Total_Points)<BR><BR>MyConn.Close<BR>Set MyConn = Nothing<BR>%&#062;<BR>---------------------------------------------------<BR><BR>Any advice would be great!<BR>Thanks,<BR>JF

  2. #2
    RipCat Guest

    Default RE: Adding up numeric values that are pulled from

    I would&#039ve thought that what you wrote would work...I thought the sum() only summed what the WHERE clause filtered...I would Response.Write my sql and make sure I was getting the correct value to select for...If you are...Then...<BR><BR>I can offer a long winded solution...I usually use getRows instead of keeping recordset open...But...You could loop through recordset just as easily...<BR><BR>Dim laPointsArray<BR>Dim lnI<BR>Dim Total_Points<BR><BR>sSQL = "SELECT point FROM tblmarketinfo WHERE market=&#039" & vmarket1 & "&#039"<BR><BR>Set RS = MyConn.Execute(sSQL)<BR><BR>laPointsArray = Rs.GetRows <BR><BR>&#039Index is backward (cols,rows) and begins at 0 instead of 1<BR><BR>Total_Points = 0<BR><BR>For lnI = 0 TO UBOUND(laPointsArray,2)<BR><BR> Total_Points = Total_Points + RS(0,lnI)<BR><BR>Next<BR><BR>Keeping RS Open:<BR><BR>Do While Not Rs.Eof()<BR><BR> Total_Points = Total_Points + RS(0)<BR><BR> Rs.MoveNext<BR><BR>End<BR><BR>This is the long way around though...Hope this helps...<BR><BR>RipCat<BR><BR><BR>

  3. #3
    RipCat Guest

    Default Oops...

    Oops...MyBad...<BR><BR>This line in previous post in for loop:<BR><BR>Total_Points = Total_Points + RS(0,lnI)<BR><BR>Should&#039ve been:<BR><BR>Total_Points = Total_Points + laPointsArray(0,lnI)<BR><BR>RipCat

Posting Permissions

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