If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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