counting DB entry values (not rows)

Discussion in 'ASP General' started by Lord Merlin, May 14, 2004.

  1. Lord Merlin

    Lord Merlin Guest

    How do I add up the values of the data from all of the rows returned? i.e -
    I have a DB, with 148 rows of client info, and I want to addup all the
    scores. Each entry's score differes. Here is the code I have sofar:

    SQL = "select * from company_scores order by company_name"
    Set objRS = Server.CreateObject("ADODB.Recordset")

    objRS.CursorLocation = adUseServer
    objRS.CursorType = adOpenStatic
    objRS.ActiveConnection = objConn
    objRS.open SQL, objConn

    i=0
    icount = 0
    Do until objRS.EOF


    company_name = objRS("company_name")


    'currentday = day(objRS("sDate"))
    'currentmonth = month(objRS("sDate"))
    'currentyear = year(objRS("sDate"))

    'Select Case currentmonth
    'Case 1 iMonth = "January"
    'Case 2 iMonth = "February"
    'Case 3 iMonth = "March"
    'Case 4 iMonth = "April"
    'Case 5 iMonth = "May"
    'Case 6 iMonth = "June"
    'Case 7 iMonth = "July"
    'Case 8 iMonth = "August"
    'Case 9 iMonth = "September"
    'Case 10 iMonth = "October"
    'Case 11 iMonth = "November"
    'Case 12 iMonth = "December"
    'End Select

    'thecurrentdate = Cstr(currentday) & " " & iMonth & " " &
    Cstr(currentyear)
    on error resume next

    iCompliments = objRS("compliments")
    iComplaints = objRS("complaints")
    If iCompliments < 1 Then iCompliments = 0
    If iComplaints < 1 Then iComplaints = 0
    If isnull(iCompliments) Then iCompliments = 0
    If isnull(iComplaints) Then iComplaints = 0
    tCompliments = iCompliments + iCompliments
    tComplaints = iComplaints + iComplaints

    If isnull(objRS("score")) then
    myendscore = "No data yet"
    Else
    myendscore = objRS("score") & "/" & 10
    End If

    if i=0 then
    tdcolor="#CEC6D6"
    i=1
    Else
    tdcolor="#BCB1C7"
    i=0
    End if
    %>
    <TR>
    <TD COLSPAN="5"><IMG SRC="images/blank.gif" WIDTH=2
    HEIGHT=2></TD>
    </TR>
    <TR>
    <TD WIDTH="862" VALIGN="top" BGCOLOR="<%=tdcolor%>">
    <TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0">
    <TR>
    <TD WIDTH="7"><IMG SRC="images/blank.gif" WIDTH="7"
    HEIGHT="3"></TD>
    <TD VALIGN="top" ALIGN="left" CLASS="cwc">
    <%company_name=trim(company_name)

    %> <B><SPAN CLASS="link1"><A HREF =
    "cwcsearch.asp?searchby=1000&location=&city=&Country=0&supplier=<%=company_n
    ame%>&keyword=&industry=non&alias=&cwc=Y" TITLE="Click here to see recent
    comments">
    <%=company_name%></A></SPAN></B> </TD>
    </TR>
    </TABLE></TD>
    <TD WIDTH="45" ALIGN="RIGHT" VALIGN="middle"
    BGCOLOR="<%=tdcolor%>" CLASS="cwc"><%=iCompliments%></TD>
    <TD WIDTH="45" ALIGN="RIGHT" VALIGN="middle"
    BGCOLOR="<%=tdcolor%>" CLASS="cwc"><%=iComplaints%></TD>
    <TD WIDTH="7" BGCOLOR="<%=tdcolor%>" CLASS="cwc"><IMG
    SRC="images/blank.gif" WIDTH="7" HEIGHT="3"></TD>
    <%
    icount = icount +1
    ObjRS.Movenext
    Loop



    I now want to display the total amount of Compliments and the total amount
    of Complaints.As you can see, i tried the following approach, but it didn't
    work out:

    iCompliments = objRS("compliments")
    iComplaints = objRS("complaints")
    If iCompliments < 1 Then iCompliments = 0
    If iComplaints < 1 Then iComplaints = 0
    If isnull(iCompliments) Then iCompliments = 0
    If isnull(iComplaints) Then iComplaints = 0
    tCompliments = iCompliments + iCompliments
    tComplaints = iComplaints + iComplaints

    Thanx :)

    --

    Kind Regards
    Rudi Ahlers
    +27 (82) 926 1689

    Greater love has no one than this, that he lay down his life for his friends
    (John 15:13).
     
    Lord Merlin, May 14, 2004
    #1
    1. Advertising

  2. Lord Merlin

    Ray at Guest

    "Lord Merlin" <SP4M_Rudi@SP4M_Bonzai.org.za_SP4M> wrote in message
    news:...
    > How do I add up the values of the data from all of the rows returned?

    i.e -
    > I have a DB, with 148 rows of client info, and I want to addup all the
    > scores. Each entry's score differes. Here is the code I have sofar:


    I think you could do this with all one a single record resultset, like:

    SQL = "SELECT SUM(compliments),SUM(complaints) FROM company_scores"
    objConn.Open <your connection string>
    Set objRS = objConn.Execute(SQL)
    iCompliments = objRS.Fields.Item(0).Value
    iComplaints = objRS.Fields.Item(1).Value
    objRS.Close : Set objRS = Nothing
    objConn.Close : Set objConn = Nothing

    Ray at work
     
    Ray at, May 15, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. AtomicBob
    Replies:
    14
    Views:
    938
    Toby Inkster
    May 2, 2006
  2. =?Utf-8?B?RGFuaWVs?=
    Replies:
    2
    Views:
    339
    =?Utf-8?B?RGFuaWVs?=
    Feb 27, 2007
  3. Mike Owen

    Allowing entry of a Carriage Return during data entry

    Mike Owen, Jul 27, 2006, in forum: ASP .Net Web Controls
    Replies:
    3
    Views:
    750
    Alessandro Zifiglio
    Jul 27, 2006
  4. edwardfredriks

    counting up instead of counting down

    edwardfredriks, Sep 6, 2005, in forum: Javascript
    Replies:
    6
    Views:
    213
    Dr John Stockton
    Sep 7, 2005
  5. Replies:
    8
    Views:
    214
    Dennis Lee Bieber
    Dec 19, 2012
Loading...

Share This Page