...

View Full Version : SQL SUM and Average



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

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


then you can get at it with

yourRecordsetObject.Fields("ReturnedValue ")

fractalvibes
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("SalAverage")
rs("FatCat") and so forth

fv






fv

hughesmi
12-21-2004, 11:24 AM
thanks

hughesmi
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
rsReport.Close
Set rsReport = Nothing
Set adoCon = Nothing

%>

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

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


fv

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

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

fv



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum