Select Count(*)

[JohannIcon]

I know this is a silly question but I forgot how to use Select Count(*), its been quite a while now.

I am using it like this:-

sqlCount="SELECT COUNT(*) AS TotCount FROM pollvoters WHERE pollID=" & pollid
set rsCount=cnn.execute(sqlCount)
response.Write(TotCount)

But nothing is displayed on the screen. At the moment I have no entries in the database, but isn't it supposed to return 0?
Thanks for your help and time

 

[gpzcrasher]

Drop the "AS TotCount" from the SQL statement. The result set will contain the number of records.


--------------------------------
If it ain't broke, don't fix it!

 

[n2ckb]

should the last line not be...

response.write rsCount("TotCount&quot?

Nick

 

[JohannIcon]

upppppps your right n2ckb!

Thanks

 

[JohannIcon]

Another problem please, I wish to make the variable dynamic so that I can get all the values. What i have done is like this:-

rs1.MoveFirst
n=1
do
PollAnsID = rs1("PollAnsID&quot
PollAnsText = rs1("PollAnsText&quot

'get the count of all the votes for this pollID per AnserID
sqlCount="SELECT COUNT(*) AS TotVotes FROM pollvoters WHERE pollID=" & pollid
set rsCount=cnn.execute(sqlCount)
TotVotes = (rsCount("TotVotes&quot)
response.Write(PollAnsText & " has got " & TotVotes)
n = n+1

rs1.MoveNext
if rs1.eof then
exit do
End If
loop until rs1.eof

But I have an error in the SQL. How can I arrange that?

Thanks for your help!

 

[JamesLean]

The error is due to the fact you are using "totvotes" as the column name in the resultset. You can't use parentheses like that.

What is the table structure? I think you can probably return each poll with the count in one SQL statement (using a GROUP BY clause) rather than loop through for each poll.

--James

 

[JohannIcon]

My table structure is like this:-

pollID,pollAnsID,userID.

Each time the user votes, I add an entry with his userid, id of the question and id of the answer.

Thanks

 

[JamesLean]

OK, to get the total number of votes for each question use:

SELECT pollid, COUNT(*) AS votes
FROM table
GROUP BY pollid
ORDER BY pollid

To get number of votes for each answer for a particular question use:

SELECT pollansid, COUNT(*) AS votes
FROM table
WHERE pollid = 123
GROUP BY pollansid
ORDER BY pollansid

Also, to get a full list of total votes for each answer to each question use:

SELECT pollid, pollansid, COUNT(*) AS votes
FROM table
GROUP BY pollid, pollansid
ORDER BY pollid, pollansid

--James

 

[JohannIcon]

Thanks James, I did this now:-

rs1.MoveFirst
n=1
do
PollAnsID = rs1("PollAnsID&quot
PollAnsText = rs1("PollAnsText&quot

'get the count of all the votes for this pollID per AnserID
sqlCount="SELECT pollansid, COUNT(*) AS votes FROM pollVoters WHERE pollid =" & pollid & " GROUP BY pollansid ORDER BY pollansid"
set rsCount=cnn.execute(sqlCount)
Votes = (rsCount("Votes&quot)
response.Write(PollAnsText & " has got " & Votes)
n = n+1

rs1.MoveNext
if rs1.eof then
exit do
End If
loop until rs1.eof

However since Votes =1, it is displaying 1 for all the questions. Am I displaying them the wrong way?

 

[JamesLean]

What does rs1 contain (maybe post the SQL statement)? What value does pollid contain? Basically, what are you trying to do with this code?

--James

 

[JohannIcon]

Ok here is my code:-

'*** Insert the answer into the pollvoters table
sql="insert into pollvoters(pollID,pollAnsID,userid)"
sql = sql & " values(" & pollid & "," & PollChosen & "," & userid & &quot"
set rs=cnn.execute(sql)

'calculation of percentages:-
'get the count of all the votes for this pollID
sqlCount="SELECT COUNT(*) AS TotCount FROM pollvoters WHERE pollID=" & pollid
set rsCount=cnn.execute(sqlCount)

'now get all the the pollAnswers from the database for this question
Set rs1=Server.CreateObject("ADODB.Recordset&quot
rs1.ActiveConnection = cnn

sql1 = "SELECT * from pollAnswers where PollID=" & PollID
sql1=sql1 & " ORDER BY pollAnsID ASC"
'response.Write(sql1)
rs1.Open sql1, cnn, adOpenStatic, adLockReadOnly, adCmdText

'loop through all the recordset and record the amount of votes there are in the pollVoters for that particular AnsID and PollID
rs1.MoveFirst
n=1
do
PollAnsID = rs1("PollAnsID&quot
PollAnsText = rs1("PollAnsText&quot

'get the count of all the votes for this pollID per AnserID
sqlCount="SELECT pollansid, COUNT(*) AS votes FROM pollVoters WHERE pollid =" & pollid & " GROUP BY pollansid ORDER BY pollansid"
set rsCount=cnn.execute(sqlCount)
Votes = (rsCount("Votes&quot)
response.Write(PollAnsText & " has got " & Votes)
n = n+1

rs1.MoveNext
if rs1.eof then
exit do
End If
loop until rs1.eof

 

[JohannIcon]

Ok I solved it James. I did a search in the pollVoters table to see if the pollAnsID exists in it. If it does not, the Vote is automatically 0. If it does exist, get the count. Here is the code I added:-

'check wether the pollAnsID is in the pollVoters table first
sqlAnsID="SELECT * FROM pollvoters WHERE pollAnsID=" & pollAnsID
set rsAnsID=cnn.execute(sqlAnsID)

if NOT rsAnsID.EOF or NOT rsAnsID.BOF then
'get the count of all the votes for this pollID per AnserID
sqlCount="SELECT pollansid, COUNT(*) AS votes FROM pollVoters WHERE pollid =" & pollid & " AND pollAnsID =" & pollAnsID & " GROUP BY pollansid ORDER BY pollansid"
set rsCount=cnn.execute(sqlCount)
Votes = (rsCount("Votes&quot)
response.Write(PollAnsText & " has got " & Votes)
else
response.Write(PollAnsText & " has got 0 Votes&quot
n = n+1
end if

 

[JamesLean]

Hmm.. you've basically coded a standard SQL join using VBScript! I think this should get what you want, plus only needs a single trip to the DB:

'do insert of vote here (as you have it)

'now get all the the pollAnswers from the database for this question
strSQL = "SELECT a.pollanstext, COUNT(*) AS votes " &_
  "FROM pollvoters v JOIN pollanswers a ON v.pollansid = a.pollansid " &_
  "WHERE v.pollid = " & pollid & " " &_
  "GROUP BY a.pollanstext ORDER BY a.pollanstext"

Set rsCount = cnn.Execute(strSQL)

Do Until rsCount.EOF
  Response.Write rsCount(&quot;pollanstext&quot;) & &quot; has got &quot; & rsCount(&quot;votes&quot;) & &quot; votes<br />&quot;
  rsCount.MoveNext
Loop

--James

 

[JohannIcon]

Oh Thanks.

So my solution is not efficient right?

 

[JamesLean]

Not nearly as efficient as it could be (see my example). A general rule is to try and keep database hits to a minimum. With your original method you were making a seperate hit and query for each answer for the question. Imagine if you started getting 10+ possible answers!

My example makes one query and returns a simple resultset containing a row for each question and the number of hits. It is more efficient and a lot quicker.

--James

 

[JohannIcon]

Ah Yes I get your point James

Thanks

 

[JohannIcon]

James I got the following error from your query

Syntax error in FROM clause.

strSQL = "SELECT a.pollanstext, COUNT(*) AS votes " &_
"FROM pollvoters v JOIN pollanswers a ON v.pollansid = a.pollansid " &_
"WHERE v.pollid = " & pollid & " " &_
"GROUP BY a.pollanstext ORDER BY a.pollanstext"

 

[JamesLean]

Try adding an AS:

...FROM pollvoters AS v JOIN pollanswers AS a ON v.pollansid = a.pollansid...

If not, which DBMS are you using?

--James

 

[JohannIcon]

Still the same error. I am using ACCESS 2000

 

[JamesLean]

Sorry, you need to specify the INNER join in Access:

...FROM pollvoters v INNER JOIN pollanswers a ON v.pollansid = a.pollansid...

--James