Print Page | Close Window

AARRRGGGHHH!! Groupby or sum or count???

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=2952
Printed Date: 29 March 2026 at 10:15am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: AARRRGGGHHH!! Groupby or sum or count???
Posted By: Nischint
Subject: AARRRGGGHHH!! Groupby or sum or count???
Date Posted: 22 May 2003 at 12:39pm

I have a really bad and irritating problem and would love some help.

I have a table which contains:

1) storyratingid - AutoNumber
2) storyrating - Number entered into database by user
3) visitorip - IP of the user who rated the story
4) storyid - ID of the story that was rated
5) storytitle - The title of the story that was rated

Now I'm trying to show a top rated asp page "top.asp" which will show the top rated stories in order of descending ratings, what the average rating is, and how many users rated that story.

So far, I've come up with a way to show stories which have a rating higher than 4 out of 10. Here it is:

<%@ language = vbscript %>
<%
strServerConnection = "DRIVER={Microsoft Access Driver (*.mdb)};

DBQ="&server.mapPath("data.mdb")

set con = server.createObject("ADODB.Connection")
con.open(strServerConnection)

sqlString = "Select storytitle, storyid, storyrating from ratingdb Where storyrating > 4"

set rs = con.execute(sqlString)

do while not rs.eof

    storyid = rs("storyid")
    storytitle = rs("storytitle")
    storyrating = rs("storyrating")
%>
<%=storytitle%> - Rating: <%=storyrating%>/10 by "n" visitors
  <%
    rs.MoveNext
  loop
  rs.Close
%>

But I can't find a way to make sure that the highest rated is displayed first, and nor can this script show the number of users who voted for that story, represented by "n".

HELP!!!!!!!!!!



-------------
A better idiot

http://www.jamlikethat.com">Jam Like That



Replies:
Posted By: aero
Date Posted: 22 May 2003 at 12:51pm

try this

<%@ language = vbscript %>
<%
strServerConnection = "DRIVER={Microsoft Access Driver (*.mdb)};

DBQ="&server.mapPath("data.mdb")

set con = server.createObject("ADODB.Connection")
con.open(strServerConnection)

sqlString = "Select * from ratingdb Where storyrating > 4 desc"

set rs = con.execute(sqlString)

do while not rs.eof

    storyid = rs("storyid")
    storytitle = rs("storytitle")
    storyrating = rs("storyrating")
%>
<%=storytitle%> - Rating: <%=storyrating%>/10 by "n" visitors
  <%
    rs.MoveNext
  loop
  rs.Close
%>



Posted By: farrukh
Date Posted: 22 May 2003 at 12:55pm
sqlString = "Select storytitle, storyid, storyrating from ratingdb Where storyrating > 4 order by storyrating desc

this will work


-------------
i have collected some nice avatars (37) and smileys (227) here you can download
http://www24.brinkster.com/webmastertool/download.html


Posted By: aero
Date Posted: 22 May 2003 at 2:17pm

Yes I also think so  i just forget the order by storyrating

 



Posted By: ljamal
Date Posted: 22 May 2003 at 2:31pm
Actually what I think is wanted is the average rating and the number of votes that contributed to the average rating.
The average is found by:
select storyid, avg(storyrating) as ratingavg from ratingdb group by storyID

The number of votes for each story is found by:
select storyid, count(storyID) as storyIDCOunt from ratingdb group by storyID

I would then join the two together on storyID and add the storytitle to the query.

The easiest method to that from the what you have provide would be tosave the first 2 queries as views (let's call them view1 & view2) and then create another view which contains the story name (view3)

select distinct(storyID) as storyID, storytitle from ratingdb group by storytitle

and then join them all together

select view3.*, view1.ratingavg, view2.storyIDcount from view1, view2, view3 where view3.storyID = view1.storyID and view3.storyID=view2.storyID order by ratingavg desc, storyIDcount desc


-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Nischint
Date Posted: 22 May 2003 at 3:51pm

ljamal, you've got the right idea!!!

Now if I could just get the code right...

sqlString= "select storyid, avg(storyrating) as ratingavg from ratingdb group by storyid as view1, "
sqlString= sqlString & "select storyid, count(storyid) as storyidcount from ratingdb group by storyid as view2, "
sqlString= sqlString & "select distinct(storyid) as storyid, storytitle from ratingdb group by storytitle as view3, "
sqlString= sqlString & "select view3.*, view1.ratingavg, view2.storyidcount from view1, view2, view3
where view3.storyid = view1.storyid and view3.storyid=view2.storyid order by ratingavg desc, storyidcount desc"

Am I right?? Could you post the full code??



-------------
A better idiot

http://www.jamlikethat.com">Jam Like That


Posted By: ljamal
Date Posted: 22 May 2003 at 3:57pm
In the database (I'm assuming Access) click views and create view1, view2, and view3 and then you be able to use ASP to run the last join query.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Nischint
Date Posted: 22 May 2003 at 4:09pm
What??

I'm using Access, but the view tab does not contain views. It does however contain queries. Is that what you mean??

How do I do this??? I'm totally lost now!!

-------------
A better idiot

http://www.jamlikethat.com">Jam Like That


Posted By: ljamal
Date Posted: 22 May 2003 at 4:16pm
Sorry it been a while since I used Access.
Under view create new queries using SQL statements and name them view1, view2, view3 and then use ASP to run the last SQL statement.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Nischint
Date Posted: 22 May 2003 at 4:24pm
Okay, I'm using Access 2002, but although I can create queries, I can't make them using SQL statements.

I really don't know how to make this work. Please help!!

-------------
A better idiot

http://www.jamlikethat.com">Jam Like That


Posted By: ljamal
Date Posted: 22 May 2003 at 4:43pm
Click on create a new query and it should ask you to add a table, pick any one it doesn't matter. After that you'll be taken to the query builder, look around under the menus for the SQL query builder, there may even be a button that says SQL, once you find it click it and you'll be able to add the view using SQL.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Nischint
Date Posted: 22 May 2003 at 5:27pm

Okay, okay, I think I got it right. I made the queries, and did all you said. My last query, view3, is:

SELECT DISTINCT (storyid) AS storyid, storytitle
FROM ratingdb
GROUP BY storytitle;

But when I try to run the top.asp file, it shows me this error:

Circular reference caused by alias 'storyid' in query definition's SELECT list

What does that mean?? Then when I try to open that query in the database, it gives me the same error. What am I doing wrong?



-------------
A better idiot

http://www.jamlikethat.com">Jam Like That



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net