View Full Version : SQL SUM and Average
12-17-2004, 02:43 PM
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, 03:53 PM
sql = "SELECT SUM(FeildName) as ReturnedValue FROM TableName"
then you can get at it with
12-18-2004, 02: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, 12:24 PM
12-22-2004, 04: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:
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:
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")
'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, 04:32 PM
Which one is line 49???
Also do this after setting the value of your sql:
and lets se what you have....
12-22-2004, 06:09 PM
line 49 is just under <% = rsReport("DisplayTotal")%> that why i cant make sense of it!
12-22-2004, 06: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....
Powered by vBulletin® Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.