[thelist] ASP count

[rudy]

> Is this a reasonable way of achieving this (it works after all)
> but it is making 2 requests to the db which seems like a waste
> of resources.

hi kevin

excellent question

i wouldn't worry about the waste -- it's only electrons, and they are
neither created nor destroyed

consider the app's performance and response time, though, and it may be
worth the effort to look for other ways

first, you are getting a count which really represents the number of rows
returned by the other query

in coldfusion one would simply use the value of otherquery.RecordCount, a
special variable made just for this purpose, and ASP has a similar variable
(i don't know what it is, but i know it exists)

even if a special variable didn't exist, you could always obtain a count by
looping over the rows in the result set

> Is it possible to do this in one SQL statement and, if so,
> will it make any kind of significant difference?

"significant" can be measured in two ways, performance and code cleanliness

performance will depend on your system, and may or may not be measurable

code cleanliness, though, is something that perhaps everyone can relate to

as to your question, yes, it can be done in one statement

suppose you are interested not in a count but a sum

there's no special variable for that, so most people do summing when
processing the detail rows in the result set

a tougher challenge would be a count or sum for each group along with
details for each group

that usually results in code that you could describe as messy

if the group sums could be produced by the database, then some of that
messiness can be eliminated

the general strategy is to have two queries (detail and grouped) combined
with a UNION

there's an example here --

  Group totals and details in one database query
  http://r937.com/grouptotals.htm


rudy