View Full Version : SQL SUM and Average

12-17-2004, 01:43 PM
Hi all.

Could some one please give me an example of how you add use SQL: TO Sum,Average.

I kinda have worked out this;

sql = "SELECT SUM(FeildName) as FROM TableName"

But I can't this to work.

12-17-2004, 02:53 PM
sql = "SELECT SUM(FeildName) as ReturnedValue FROM TableName"

then you can get at it with

yourRecordsetObject.Fields("ReturnedValue ")

12-18-2004, 01:53 AM
Yep - remember that FROM is an SQL reserved word....

As spud points out, it is also important to give a name to that value

select AVG(salary) as SalAverage from mytable
Select SUM(salary) as ALotOfMoney from mytable
Select Count(emp_ID) as NbrEMployees from mytable
Select MIN(salary) as PoorSlob from mytable
Select MAX(salary) as FatCat from mytable

supposing rs is your recordset object, refer to as
rs("FatCat") and so forth



12-21-2004, 11:24 AM

12-22-2004, 03:03 PM
Thanks for your previous examples. :D

But I'm having a few probs. I have tried to follow your examples to the tee, however i'm gettign an error meeasge saying,(see below) and on line 49 there is notihng on it, so yes I'm confuesd :confused:

Error Type:
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment
read.asp, line 49

Could some please point out where I'm going wriong? :confused:

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsReport 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")

NameID= Clng(request.QueryString("NameID"))

'Create an ADO recordset object
Set rsReport = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database

'strSQL = "SELECT * FROM Data WHERE NameID=" & NameID & " ORDER BY Date_Late"

strSQL = "SELECT SUM(Late) as DisplayTotal FROM Data WHERE NameID=" & NameID

'Open the recordset with the SQL query
rsReport.Open strSQL, adoCon

<% = rsReport("DisplayTotal")%>
<!-- Line 49 --!>

'Reset server objects
Set rsReport = Nothing
Set adoCon = Nothing


12-22-2004, 03:32 PM
Which one is line 49???

Also do this after setting the value of your sql:
Response.Write strSQL
and lets se what you have....


12-22-2004, 05:09 PM
line 49 is just under <% = rsReport("DisplayTotal")%> that why i cant make sense of it!

12-22-2004, 05:17 PM
do a response.write of your sql and see what you built - do you have a valid name id?

After you write it out, cut and paste that sequel into whatever software you have to run queries against that database.

Also try Response.write CStr(rsReport("DisplayTotal")) if your sql proves to be valid....