Okay, I did like this, cause it's multiple fields and values:



SQL2= "SELECT SUM(graphics) / COUNT(gameid) AS avggraphics, SUM(sound) /

COUNT(gameid) AS avgsound, SUM(gameplay) / COUNT(gameid) AS avggameplay,

SUM(overall) / COUNT(gameid) AS avgoverall FROM vote WHERE gameid = '" &

objRS("gametitle") & "'"



The only prob is that it doesn't give a value... =) What's wrong? There are

some numbers in those fields... Please help! =)

--

Martin Johansson





-----Ursprungligt meddelande-----

Från: Håkan Frennesson [mailto:hakan@c...]

Skickat: den 27 september 2001 22:07

Till: ASP Databases

Ämne: [asp_databases] SV: Vote System, probs again =)





Hi!



I guess each article has a unique articleID, so you should use that ID in

the WHERE criteria:

"SELECT SUM(rating) / COUNT(Articleid) AS avgRating FROM tblRating WHERE

articleID

= " & choiceofArticleID

if you have normalized your database that is. If you have articleID in

different fields in the same table you are in deep s*t..:-)



Hakan



-----Ursprungligt meddelande-----

Från: SD-Studios [mailto:info@s...]

Skickat: den 27 september 2001 20:54

Till: ASP Databases

Ämne: [asp_databases] Vote System, probs again =)





Got one more problem. I've got different sums to get an average value of.

For example i've got an article that's called article1 and another that's

called article2. Now here's the problem. If I use Count(*) or

Count(whatever), it wont give me the right result. Cause it counts both

fields where article is either article1 or article2. How do I solve this? I

just want it to divide by the number of votes for article1 and article2

respectively. Please help! =)

--

Martin Johansson





-----Ursprungligt meddelande-----

Från: Kyle Burns [mailto:kburns@c...]

Skickat: den 25 september 2001 19:37

Till: ASP Databases

Ämne: [asp_databases] RE: SV: RE: Vote System





Even better than Count(*), which will also give a performance hit, is to use

the Count function on a field that you know will contain a value (nulls are

skipped in Count) such as CodeID.  I also noticed that you are referencing

the value of the field "Average" after the recordset object is closed and

set to Nothing.  You'll need to modify the code to save this value to a

variable before closing the recordset.  I kind of shy away from using words

that could ever conceivably be a keyword when I write my SQL statements.  My

suggestion would be to have your SQL statement end up something like this:



"SELECT SUM(rating) / COUNT(codeid) AS avgRating FROM tblRating WHERE codeid

= " & choice





=================================

Kyle M. Burns, MCSD

ECommerce Technology Manager

Centra Credit Union

kburns@c...









-----Original Message-----

From: SD-Studios [mailto:info@s...]

Sent: Tuesday, September 25, 2001 11:21 AM

To: ASP Databases

Subject: [asp_databases] SV: RE: Vote System





Okay! Thanks! =)

--

Martin Johansson





-----Ursprungligt meddelande-----

Från: David Cameron [mailto:dcameron@i...]

Skickat: den 25 september 2001 01:30

Till: ASP Databases

Ämne: [asp_databases] RE: Vote System







Whoa there. There are faster ways of doing that.



First comment, never use recordcount. There is a page about it on Ken's site

(www.adopenstatic.com), but basically to get a true result you need to move

last and move first, which is a performance loss. COUNT(*) in the SQL

statement is always a better option.



<%

 dim averagevote



 Set rsd2 = Server.CreateObject("ADODB.Recordset")

 strSQL= "SELECT * FROM tblRating WHERE CodeID = " & choice

 strSQL = "SELECT SUM(rating)/COUNT(*) as Average " & _

   "FROM tblRating WHERE CodeID = " & choice

 rsd2.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

 rsd2.close

 Set rsd2 = Nothing

 averagevote = int(rsd2("Average"))

 If averagevote < 1 Then averagevote = 1



%>

                    <img src="images/<%=averagevote%>stars.gif" width="62"

height="12">



I checked the SQL string for SQL Server, it should work in Access. If not

you can return the sum and 2 different fields for Count and Sum and do the

calculation in the page.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Sam Clohesy [mailto:sam@e...]

Sent: Tuesday, 25 September 2001 1:06 AM

To: ASP Databases

Subject: [asp_databases] RE: Vote System





Something like this:



<%

 dim totalvote

 dim votecount

 dim averagevote





 Set rsd2 = Server.CreateObject("ADODB.Recordset")

 strSQL= "SELECT * FROM tblRating WHERE CodeID = " & choice

 rsd2.Open strSQL, conn, 1'adOpenForwardOnly

 votecount = rsd2.recordcount



 do while NOT rsd2.EOF

  totalvote = totalvote + rsd2("rating")

  rsd2.movenext

 loop

 if votecount > 0 then

 averagevote = int(totalvote/votecount)+1

 end if



 %>

                    <img src="images/<%=averagevote%>stars.gif" width="62"

height="12">



No doubt better way...



Thanks



Sam

r u sure the gametitle exists?



Another tip: If the obRs.fields("gametitle").value contains the ' 

character

then your SQL will fail.  Use ...gameid =3D '" &

replace(objRS("gametitle"),"'","''") & "'"



Another thought - you're matching gameid to gametitle... is this right?

Don't you want to match gameid to gameid?  Just a guess...



> -----Original Message-----

> From: SD-Studios [mailto:info@s...]

> Sent: 28 September 2001 14:37

> To: ASP Databases

> Subject: [asp_databases] SV: SV: Vote System, probs again =3D)

>

>

> Okay, I did like this, cause it's multiple fields and values:

>

> SQL2=3D "SELECT SUM(graphics) / COUNT(gameid) AS avggraphics,

> SUM(sound) /

> COUNT(gameid) AS avgsound, SUM(gameplay) / COUNT(gameid) AS

> avggameplay,

> SUM(overall) / COUNT(gameid) AS avgoverall FROM vote WHERE

> gameid =3D '" &

> objRS("gametitle") & "'"

>

> The only prob is that it doesn't give a value... =3D) What's

> wrong? There are

> some numbers in those fields... Please help! =3D)

> --

> Martin Johansson

>