counter in a recordset

[storm1976]

hi there,

just asking for a bit of advice really.

i have got a recordset and would like to look through it counting through the number of times a status comes up. the status at the moment is a range of 0-4 but this could be changed.

for each number of the range i would like to count the recordset for each appearance and give out the final value.

i did at first use a select case function but it was hard coded rather than dynamic so could not be added to in the future.

have been told to look at recursive programming to create a function but not sure which way to go round it.

hope there are people out there who have got some ideas (and also some who have actually understood what ive been waffling about)

cheers in advance
Storm1976

 

[JamesLean]

I would be inclined to issue a seperate query against the DB in this situation rather than use ASP to calculate aggregate totals:

SELECT status, COUNT(*) AS stat_cnt
FROM tablename
GROUP BY status

This will mean that if you add more statuses in the future you don't need to change any code. --James

 

[storm1976]

cheers for the quick reply jameslean but i guess i didnt make myself to clear in the first post. apologies.

i have a database table called contacts, in this table each contact could have a different statusid (ranging from 0-6, but may change later on and is linked to another table called status)

what i want to happen is going through the database for each statusid it should count the number of contacts who are at that status and give the result. then do the same for the next statusid and so on.

so could end up with:

status1 = 0
status2 = 120
status3 = 43

etc.

hope its been explained a little bit clearer.

cheers
Storm1976

 

[JamesLean]

OK, your query would look something like this then:

SELECT statusid, COUNT(*)
FROM contacts
GROUP BY statusid
ORDER BY statusid

Or, to list the description of the status rather than just the id you could use:

SELECT s.statdesc, COUNT(*)
FROM contacts c JOIN status s ON c.statusid = s.statusid
GROUP BY s.statdesc
ORDER BY s.statdesc

--James

 

[storm1976]

doesnt that count the number of statusids?

would like to have the number of idividual statusids unless im getting the information from the query wrong

sorry about this s

 

[JamesLean]

No, this will give you a resultset containing a row for each status and the number of contacts who are at that status. Say you had a contacts table like this:

ContactID | StatusID
1 | 1
2 | 4
3 | 4
4 | 5
5 | 2
6 | 1
7 | 2
8 | 3

Executing the first query from my last post would return:

StatusID | Count
1 | 2
2 | 2
3 | 1
4 | 2
5 | 1

Try it on your tables and see what I mean! --James

 

[storm1976]

cheers for that james but there may come a time when there wont be any contacts in the database with a statusid.

how would i be able to include these fields?

i misposted this the first time in the VBscript forum and they mentioned a two dimensional array.

thing with that is wouldnt know where to start

cheers

 

[JamesLean]

The query will return a row for each distinct statusid (including NULL) and a count of how many there are, so you don't need to change anything:

StatusID | Count
NULL | xx
1 | 2
2 | 2
3 | 1
4 | 2
5 | 1 --James