Count number of entries in db.

[Mhtml]

How can I count the total number of entries in a database?
And the total number of entries matching a certain criteria,

eg: IE5.0 will count all the number of fields in the Browser coloumn which equal IE5.0 ?

[glenngv]

brw = "IE5.0"
sql = "SELECT COUNT(*) as maxcount FROM TABLE_NAME_HERE WHERE Browser='"&brw&"'"

set rs = conn.Execute(sql)

response.write rs("maxcount")

where rs is the recordset object
and conn is the connection object

[Alekz]

Hi,
To count number of recordes in a table in a databse use something like:
SELECT count(*) as records_count from Table

Alex

[Mhtml]

How would I get it to count IP? I'm not sure about the " ' marks..

[raf]

quick answer : use an sql-selectquery like
sql = "select count(*) from TABLENAME"

for criteria
sql = "select count(*) from TABLENAME where Browser='IE5.0'"

you can use an alias to give the result a columnheader
sql = "select count(*) as total from TABLENAME"


longer answer :
it depends.

their are numerous ways to go here (depending on the databaseformat, the number of entries, how often you want the count, how important the entrys themselves are, if your doing this on a production-database etc)

if you frequently need the count and your working on a big dataset (let's say 100 000 records),then it's better to create an optimized dataset (seperated from your poduction db) that already contains the info. or contains basic info so that you can compute the data you want on the fly.
you can then get this single precomputed number (dataset can be updated over night for instance)
i once faced the problem that i frequently had to recompute an average (when a new record was entered) on a table with 600 000 records. It took a few seconds to run through the hole set and get the average. By storing the average and just slightly modidfying it with each entry, i could do it in a fraction of a second. It's the same with counting.
buth like i said : the best way depends from app to app.

[raf]

whow. this goes fast. (for once i'm not the only one reacting :-)

about the ip ? what do you mean ?
about the ' (quotes). If its a text variable, the value has to be enclosed in quotes. If its a number variable, no quotes

[Mhtml]

I get an error when using ip address, 127.0.0.1 whichi is in my db on PWS.
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/odyssey/getcount.asp, line 20

sql = "SELECT COUNT(*) as maxcount FROM Counter WHERE IP=" &127.0.0.1&"'"
---------------------------------------------------------------^

[raf]

try this

sql = "SELECT COUNT(*) as maxcount FROM Counter WHERE IP='127.0.0.1'"
(if the variable IP is a text variable. probably)

or

sql = "SELECT COUNT(*) as maxcount FROM Counter WHERE IP=127.0.0.1"
(if the variable IP is numerical)

[glenngv]

Originally posted by raf
try this

sql = "SELECT COUNT(*) as maxcount FROM Counter WHERE IP='127.0.0.1'"
(if the variable IP is a text variable. probably)

or

sql = "SELECT COUNT(*) as maxcount FROM Counter WHERE IP=127.0.0.1"
(if the variable IP is numerical)


no IFs here. IP addr is a string and will never be a number.
(No number has 3 decimal points :))

sql = "SELECT COUNT(*) as maxcount FROM Counter WHERE IP='127.0.0.1'"

[raf]

your right, glenngv.
just tryed to explain and illustrate the difference and the use of the quotes.

and are you sure that no number has three 'decimal points' ? i live in belgium and we use a , as a decimal point + a . for the thousands. So on my paycheck, my boss writes 10.000.000.000,256 where yours probably would write 10,000,000,000.256.

[Alekz]

Hi,
Just a thought.
IP address fits perfectly in unsigned long datatype (4 bytes), so it can be easily stored as a number using a byte per IP address section. This way You could implement IP filtering for example using bit masks, instead of string parsing and this is much faster...

Alex

[glenngv]

Originally posted by raf
your right, glenngv.
just tryed to explain and illustrate the difference and the use of the quotes.

and are you sure that no number has three 'decimal points' ? i live in belgium and we use a , as a decimal point + a . for the thousands. So on my paycheck, my boss writes 10.000.000.000,256 where yours probably would write 10,000,000,000.256.

yes im pretty sure that no number anywhere in the world has 3 decimal points like this:

127.0.0.1

:)

[webmarkart]

As far as the counter that meets certain criteria, I use something like this...

<%
sql = ""
sql = sql & "SELECT * FROM LISTINGS "
sql = sql & "WHERE LIST_NO IS NOT NULL "
'response.write sql
Set rs = oConn.Execute(sql)

counter = 0
%>

RESULT HEADINGS

<%
If NOT rs.EOF Then
Do Until rs.EOF
%>

DISPLAY RESULTS HERE
<%= counter %> Records Met Your Search

<%
counter = counter + 1
rs.Movenext
Loop
%>

<%
rs.Close
Set rs = nothing
End If
%>

----------------------------------
If you want I can send you the file so its easier to follow...
do a search on www.tsunis.com (www.tsunis.com/listings.asp?action=search) and on the bottom of the results page it will show you what it looks like.

[raf]

if you don't mind the timewaste and spilled servercapacity, you could do just that.

buth why not let your database do this? goes a lot faster !

the best way to do this depends on numerous factors (see my first post) buth selecting all the variables of the records, opening this (potentialy verry large recordset) and looping through it will never be the best sollution if you just want a count.
I only do this for limitted recordsets and only if i want to display some info for each record

[webmarkart]

I attatched the page as an .inc file. As you will see I do return info for each record and from there they can select one to view the full details. It may not be the best way but its always worked for me.

[whammy]

If you wanted to get a count of how many times each ip is in the database:

"SELECT COUNT(DISTINCT(IP)) as IPCOUNT FROM Counter"