SUM field from open recordset after displaying all records?

[Cirrus9]

It's been a while since I have done this so I am a little rusty. Please forgive me.
I have a recordset that returns recordsets properly. One of the fields is a (money) field in the SQL db.

My task:

example: If the money field returns 10 records I need to display all records and then SUM all of the records at the bottom. Can I use an open RS to SUM the field or should I use another method

amount
-------
20.00
-------
40.00
-------
30.00
-------
90.00(Sum)


Thanks in advance.

 

[DotNetGnat]

do this at the query level...

SELECT mymoneyfld, SUM(mymoneyfld) as totalsum
from mytable

then you can display rs("totalsum") at the end of the table..


-DNG

 

[Sheco]

What database are you using?

Depending on if your database supports it you might also try adding WITH ROLLUP to your SQL query.

 

[Tarwn]

I don't think DotNetGnat's solution is going to work..? Won't the inclusion of an aggregate function in the SELECT require you to group by on the mymoneyfld? You would then get either the same value in the sum field or possibly the sum of all values that are identical, but still one record per money value.

On the other hand, you could use an inner select in your SQL statement to achieve this (though you would then have the saame total repeated for every row) or could total the values as your looping through the resultset in your code.

Inner Select:

SELECT mymoneyfld, totalsum 
FROM mytable INNER JOIN (SELECT SUM(mymoneyfld) as totalsum FROM mytable WHERE [i]conditions[/i]) inrTbl ON [i]conditions that match fields for prev conditions[/i]
GROUP BY mymoneyfield

In Code:

Set myRS = myConn.Execute(mySQLString)
Dim totalsum
totalsum = 0
If Not myRS.EOF Then MyRS.MoveFirst
Do Until myRS.EOF
   Response.Write myRS("mymoneyfld") & "<br>"
   totalsum = totalsum + myRS("mymoneyfld")
   myRS.MoveNext
Loop

Response.Write "Total: " & totalsum

As always, I would suggest using .GetRows() instead of actually looping through the recordset, but I wanted to keep the example short.

-T