Hi.
I have a table called "lic_main_table" and a column within called "commission".

The commission contains numeric information.

What I want to do is display the total commission (sum of all records) on an ASP page.

At present I can do this by using hidden text fields within a record count, and using a JS calculation script.
However, I want to know how to do this the proper way.

Any help greatly appreicated.

Gary.

Well if your pulling data straight from a database you could use a SQL Statement like this:

sSQL = "SELECT Sum(lic_main_table.commission) AS SumOfCommission
FROM lic_main_table"

Then (lets say ur recordset is just called 'rs', then just use: rs("SumOfCommission"

Hope this helps!


********************
wheatNOTmeat

Hi again.
That seems to be OK if the only column I need to display is Commission, which is great! Thanks BUT, I need to have all columns in that table present in the recordset.

Like So:
SELECT *, Sum(lic_main_table.commission) AS SumOfCommission
FROM lic_main_table
WHERE timestamp BETWEEN 'MMColParam' and 'MMColParam2' and status_stage = 'MMColParam3'


The problem is, I keep getting an error which says "Mixing of group columns MIN(),MAX(),COUN() with no group columns is Illigal where there is no GROUP BY clause.

I have tried reading about GROUP BY, but have NO idea what it means or how to make it work.

Sorry to be a pain! Any chance you could help again?

Thanks for all the effort so far!

Gary.<img src=../images/dwzone/forum/icon_smile.gif border=0 align=middle>

Hi again.
Done some reading on GROUP BY and now all working fine. It explained that group ny splits the table into "smaller groups". I needed to find the commission on different criteria, luckily this was already in the URL filter.

Have a look at the page code: All works fine now.

&lt;%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%&gt;
&lt;!--#include file="Connections/licdates.asp" --&gt;

&lt;%
var Recordset1__MMColParam = "1";
if (String(Request.QueryString("startdate") != "undefined" &&
String(Request.QueryString("startdate") != "" {
Recordset1__MMColParam = String(Request.QueryString("startdate");
}
%&gt;
&lt;%
var Recordset1__MMColParam2 = "1";
if (String(Request.QueryString("enddate") != "undefined" &&
String(Request.QueryString("enddate") != "" {
Recordset1__MMColParam2 = String(Request.QueryString("enddate");
}
%&gt;
&lt;%
var Recordset1__MMColParam3 = "1";
if (String(Request.QueryString("status_stage") != "undefined" &&
String(Request.QueryString("status_stage") != "" {
Recordset1__MMColParam3 = String(Request.QueryString("status_stage");
}
%&gt;
&lt;%
var Recordset1 = Server.CreateObject("ADODB.Recordset";
Recordset1.ActiveConnection = MM_licdates_STRING;
startDate = "123";
Recordset1.Source = "SELECT * FROM lic_main_table WHERE timestamp BETWEEN '"+ Recordset1__MMColParam.replace(/'/g, "''" + "' and '"+ Recordset1__MMColParam2.replace(/'/g, "''" + "' and status_stage = '"+ Recordset1__MMColParam3.replace(/'/g, "''" + "'";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;
%&gt;
&lt;%
var sumofcomm__MMColParam4 = "1";
if (String(Request.QueryString("status_stage") != "undefined" &&
String(Request.QueryString("status_stage") != "" {
sumofcomm__MMColParam4 = String(Request.QueryString("status_stage");
}
%&gt;
&lt;%
var sumofcomm = Server.CreateObject("ADODB.Recordset";
sumofcomm.ActiveConnection = MM_licdates_STRING;
sumofcomm.Source = "SELECT Sum(lic_main_table.commission) AS SumOfCommission FROM lic_main_table WHERE status_stage = '"+ sumofcomm__MMColParam4.replace(/'/g, "''" + "' GROUP BY status_stage";
sumofcomm.CursorType = 0;
sumofcomm.CursorLocation = 2;
sumofcomm.LockType = 1;
sumofcomm.Open();
var sumofcomm_numRows = 0;
%&gt;

&lt;%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%&gt;
&lt;%
// *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

// set the record count
var Recordset1_total = Recordset1.RecordCount;

// set the number of rows displayed on this page
if (Recordset1_numRows &lt; 0) { // if repeat region set to all records
Recordset1_numRows = Recordset1_total;
} else if (Recordset1_numRows == 0) { // if no repeat regions
Recordset1_numRows = 1;
}

// set the first and last displayed record
var Recordset1_first = 1;
var Recordset1_last = Recordset1_first + Recordset1_numRows - 1;

// if we have the correct record count, check the other stats
if (Recordset1_total != -1) {
Recordset1_numRows = Math.min(Recordset1_numRows, Recordset1_total);
Recordset1_first = Math.min(Recordset1_first, Recordset1_total);
Recordset1_last = Math.min(Recordset1_last, Recordset1_total);
}
%&gt;

&lt;%
// *** Recordset Stats: if we don't know the record count, manually count them

if (Recordset1_total == -1) {

// count the total records by iterating through the recordset
for (Recordset1_total=0; !Recordset1.EOF; Recordset1.MoveNext()) {
Recordset1_total++;
}

// reset the cursor to the beginning
if (Recordset1.CursorType &gt; 0) {
if (!Recordset1.BOF) Recordset1.MoveFirst();
} else {
Recordset1.Requery();
}

// set the number of rows displayed on this page
if (Recordset1_numRows &lt; 0 || Recordset1_numRows &gt; Recordset1_total) {
Recordset1_numRows = Recordset1_total;
}

// set the first and last displayed record
Recordset1_last = Math.min(Recordset1_first + Recordset1_numRows - 1, Recordset1_total);
Recordset1_first = Math.min(Recordset1_first, Recordset1_total);
}
%&gt;

&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;table width="720" border="0" align="center" cellpadding="0" cellspacing="0"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;font size="4" face="Verdana, Arial, Helvetica, sans-serif"&gt;&lt;%=(Recordset1.Fields.Item("status_stage".Value)%&gt;&nbsp;management report&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;table width="720" border="0" align="center" cellpadding="0" cellspacing="0"&gt;

&lt;tr align="left" valign="middle" bgcolor="#CCCCCC"&gt;
&lt;td width="8%" height="0"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;ID&lt;/font&gt;&lt;/td&gt;
&lt;td width="4%" height="0"&gt;&nbsp;&lt;/td&gt;
&lt;td width="14%" height="0"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;SURNAME&lt;/font&gt;&lt;/td&gt;
&lt;td width="24%" height="0"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Insurance
Company &lt;/font&gt;&lt;/td&gt;
&lt;td width="20%" height="0"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Policy
Number &lt;/font&gt;&lt;/td&gt;
&lt;td width="14%" height="0"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Date&lt;/font&gt;&lt;/td&gt;
&lt;td width="14%" height="0"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Commission
Due &lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;% while ((Repeat1__numRows-- != 0) && (!Recordset1.EOF)) { %&gt;
&lt;table width="720" border="0" align="center" cellpadding="0" cellspacing="0"&gt;
&lt;tr align="left" valign="top" bgcolor="#FFFFFF"&gt;
&lt;td width="8%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;&lt;%=(Recordset1.Fields.Item("ID".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="4%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;&nbsp;&lt;/font&gt;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;&lt;%=(Recordset1.Fields.Item("applicant_1_surname".Value)%&gt;&nbsp;&lt;/font&gt;&lt;/td&gt;
&lt;td width="24%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;&lt;%=(Recordset1.Fields.Item("provider".Value)%&gt;&nbsp;&lt;/font&gt;&lt;/td&gt;
&lt;td width="20%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Coming
soon... &nbsp;&lt;/font&gt;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Coming
soon... &nbsp;&lt;/font&gt;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;&pound;&lt;%=(Recordset1.Fields.Item("commission".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;%
Repeat1__index++;
Recordset1.MoveNext();
}
%&gt;
&lt;table width="720" border="0" align="center" cellpadding="0" cellspacing="0"&gt;
&lt;tr align="left" valign="top" bgcolor="#FFFFFF"&gt;
&lt;td width="8%"&gt;&nbsp;&lt;/td&gt;
&lt;td width="4%"&gt;&nbsp;&lt;/td&gt;
&lt;td width="14%"&gt;&nbsp;&lt;/td&gt;
&lt;td width="20%"&gt;&nbsp;&lt;/td&gt;
&lt;td width="24%"&gt;&nbsp;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;strong&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;Total:&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;font size="1" face="Verdana, Arial, Helvetica, sans-serif"&gt;&lt;strong&gt;&pound;&lt;%=(sumofcomm.Fields.Item("SumOfCommission".Value)%&gt;&lt;/strong&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close();
%&gt;
&lt;%
sumofcomm.Close();
%&gt;