counting DB entry values (not rows)

[Lord Merlin]

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).

 

[Ray at]

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