question about sum of field

[gcapp]

Is there a way to have the sum of a certain amount of records in an Access database display on a page?

For example if I have a table with a field that has the number of parcels in a certain venue. Each venue has a number of parcels and I want to show the total of a certain area, is there a way to do that?

If my table is called Venues and my field Parcels, the sql would be sql = "SELECT Parcels FROM Venues and ????????


What would the sql for that be??

If anyone can help, it would be appreciated.

Gary

[Morgoth]

I WIN!


<%
Dim Total
'Total = 0 so that it is not a string anymore, but an integer.
Total = 0
'You can see what I mean if you don't make Total = 0 at the begining

SQL = "SELECT field FROM table ORDER BY ID ASC"
'ORDER BY ID ASC = 1,2,3,4,5 as ORDER BY ID DESC = 5,4,3,2,1
Set RS = Conn.Execute(SQL)

Do until RS.EOF
Total = Total + RS("Field")
Loop

Response.Write Total
%>

[Roy Sinclair]

Use the Sum function in SQL:

select Sum(Parcels) AS TotalParcels from Venues

[whammy]

Sorry Morgoth, Roy wins... I use the SUM() function for billing reports at work... much better to let SQL do the work for you rather than looping. ;)

[Morgoth]

*Morgoth slaps everyone*

YOU NEED TO TELL ME THESE THINGS

Stupid ASP functions everywhere, and there is no FORMAT()

[whammy]

It's not for us to tell you; it's for you to do the research... ;)

Actually, knowing how to research is probably one of the most important (if not THE most important) skills in programming.

:D

[Morgoth]

I know how to research things, but functions drive me nuts.
And usually I am lazy.

So, yeah, I will just look up my functions before asking for now on.

[Roy Sinclair]

Originally posted by Morgoth
I know how to research things, but functions drive me nuts.
And usually I am lazy.

So, yeah, I will just look up my functions before asking for now on.

That's funny, I don't see your name at the top asking anything. You gave a solution, I just happened to be privy to something simpler (having used it recently).

[Morgoth]

But the sad part is, I knew about Sum(), I USE sum() in my poll!

*Whaaa* :(

[whammy]

Hey, at least your solution WOULD work! :)

Might be a bit slower though...

[Morgoth]

Well, whammy, acually, a function is just code that will return a value, how could it be any slower if it pretty much does the exact same thing??

Maybe it's a bit faster because the person that wrote the function did a better method, but still.

You know what I mean, right?

[whammy]

Well I know what you mean, but loops are well known to be slow.

Making multiple SQL requests inside a loop further slows everything down (although you're not exactly doing that, but you're still going through the recordset).

If you can return what you need right away with one SQL statement, it's going to run a LOT faster than looping (even if it's just going through the recordset, since SQL server can use its built-in indexing, etc.), although there are times when you have to loop (this just isn't one of them!).

Time your looping method as opposed to using SUM() and you'll see what I mean.

[Morgoth]

Yeah, I see what you mean...

But, how do I time the creation of a page?
I have seen that on PHP, but I haven't seen it on ASP, is it possible?

[whammy]

Sure... don't have any links offhand but I'll google it in a little bit when I get a chance. :D