Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts

    SQL SUM and Average

    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.
    Kind regards,
    Mike Hughes

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    sql = "SELECT SUM(FeildName) as ReturnedValue FROM TableName"


    then you can get at it with

    yourRecordsetObject.Fields("ReturnedValue ")

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    thanks
    Kind regards,
    Mike Hughes

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks for your previous examples.

    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

    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?

    Code:
    <%
    '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
    
    %>
    Last edited by hughesmi; 12-22-2004 at 05:08 PM.
    Kind regards,
    Mike Hughes

  • #6
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #7
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    line 49 is just under <% = rsReport("DisplayTotal")%> that why i cant make sense of it!
    Kind regards,
    Mike Hughes

  • #8
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •