asp database count script!

llaadd · 1 Mar 2007 at 09:05

hi, i'm looking for a script that will run in asp that checks a database, and count how many times people have selected a certain value. i.e. "blue"

i think i might have an idea how to do this, but i also need it to not count multiple choices from the same person. i.e. if person A chooses Blue on day 1 but on day 2 he changes to orange and submits it in a form to the database. This will mean person but i just need to check the final one!

thanks.

rpstewart · 1 Mar 2007 at 10:35

Are you looking to do this mainly in ASP or mainly in the database query?

Can you post the table structure and I'll have a crack at the SQL.

llaadd · 1 Mar 2007 at 10:51

it's a database with the following fields:

id_number
position1
position2
position3
position4
date
time

I need to check how many of each name there is (i.e. 5 x John Doe in position 2) but if say a person with ID 1234567 submits twice, only the last submisstion should count.

thanks a lot.

rpstewart · 1 Mar 2007 at 11:22

Having the date and time in separate fields doesn't help, ideally they should be combined into a timestamp. As a result it becomes difficult to determine the latest submission.

The SQL should be do-able but it's going to be tricky with the database structure that you have. I'll keep thinking about it and I'll let you know if I come up with anything.

llaadd · 1 Mar 2007 at 11:56

hi, well the date and time are only there for my personal use, there is also an (AutoNumber) field called ID which everything is indexed by, that will determine which is the latest. sorry i forgot to mention that!

rpstewart · 1 Mar 2007 at 13:00

OK, that makes things a lot easier.

Code:

select position2, count(*) 
from tablename
where ID in (select max(ID), id_number from tablename group by id_number)
group by position2

llaadd · 2 Mar 2007 at 11:18

thanks, ok so that will count the number of each right (if i followed it correctly!!) what is the output variable then? so i can display like this:

Votes per peron:
Position 1:
Person 1 = 5 votes
person 2 = 3 votes

Position 2:
Person 3 = 3 votes
etc

sorry if i'm just being stupid but i really can't see it!!

(or does this make a new table with just the latest vote from each person??)

rpstewart · 2 Mar 2007 at 12:55

All it does is returns all the values for position 2 with the number of occurrences of each. I've no idea of how you turn that into the output you want, I don't know ASP..

You'll need to do a separate query for each position, doing it in a single query will give you a count of each distinct combination of position values.