totalling Columns from a Query result


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: totalling Columns from a Query result

  1. #1
    Paul M Guest

    totalling Columns from a Query result


    Does anyone know how to do this:
    I have a page that displays the results of a query posted to it from another
    page.
    But I would like to be able to add a total for each column that is returned.
    e.g.
    If the query is SELECT * from Films Where Actor is like Mel gibson, this
    would return multiple rows of data, and If one of the columns is Gross Takings,
    I would a total at the bottom of the page totalling all the gross takings.

    I hope this makes sense, I have got everything else to work apart from the
    total for the columns.

    Any help would be much appreciated.

    Paul

  2. #2
    Chadwick Posey Guest

    Re: totalling Columns from a Query result


    This is possible two ways, both involve using two queries and two recordsets,
    the two ways are different in the way you access the recordsets.

    One way is to select your results, and then add another query with the same
    where clause, and an aggregate function, such as sum run against the column
    to be totaled. Example:

    Set rs = conn.execute("SELECT ActorName, [Gross Revenues] FROM Actors WHERE
    ActorName Like '%Gibson%'")
    'Do your recordset stuff here to display rs, check for returned rows, etc.

    then do another recordset further down,
    set rs = conn.execute("SELECT Sum([Gross Revenues]) as TotalRevenues FROM
    Actors WHERE ActorName like '%Gibson%'")
    Total Revenues: <%=rs("TotalRevenues")%>

    This is another way to do it. if you are running against a SQL Server, you
    can put both records in a stored procedure, or concatenate them together
    like this:

    Set rs = conn.execute ("SELECT ActorName, [Gross Revenues] FROM Actors WHERE
    ActorName Like '%Gibson%'; SELECT Sum([Gross Revenues]) as TotalRevenues
    FROM Actors WHERE ActorName like '%Gibson%';")

    then when you are done with the first recordset, use this:
    set rs = rs.NextRecordset

    to access your second recordset.

    There are other things you could do here, these are the simplest.


    Chadwick Posey
    Viajo.com

    "Paul M" <morrisp6@yahoo.co.uk> wrote:
    >
    >Does anyone know how to do this:
    >I have a page that displays the results of a query posted to it from another
    >page.
    >But I would like to be able to add a total for each column that is returned.
    >e.g.
    >If the query is SELECT * from Films Where Actor is like Mel gibson, this
    >would return multiple rows of data, and If one of the columns is Gross Takings,
    >I would a total at the bottom of the page totalling all the gross takings.
    >
    >I hope this makes sense, I have got everything else to work apart from the
    >total for the columns.
    >
    >Any help would be much appreciated.
    >
    >Paul



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center