Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - AARRRGGGHHH!! Groupby or sum or count???
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

AARRRGGGHHH!! Groupby or sum or count???

 Post Reply Post Reply Page  12>
Author
Nischint View Drop Down
Groupie
Groupie
Avatar

Joined: 26 August 2002
Location: India
Status: Offline
Points: 62
Post Options Post Options   Thanks (0) Thanks(0)   Quote Nischint Quote  Post ReplyReply Direct Link To This Post Topic: AARRRGGGHHH!! Groupby or sum or count???
    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

Jam Like That
Back to Top
aero View Drop Down
Groupie
Groupie
Avatar

Joined: 21 May 2003
Location: Denmark
Status: Offline
Points: 80
Post Options Post Options   Thanks (0) Thanks(0)   Quote aero Quote  Post ReplyReply Direct Link To This Post 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
%>

Back to Top
farrukh View Drop Down
Groupie
Groupie
Avatar

Joined: 10 May 2002
Location: Pakistan
Status: Offline
Points: 147
Post Options Post Options   Thanks (0) Thanks(0)   Quote farrukh Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
aero View Drop Down
Groupie
Groupie
Avatar

Joined: 21 May 2003
Location: Denmark
Status: Offline
Points: 80
Post Options Post Options   Thanks (0) Thanks(0)   Quote aero Quote  Post ReplyReply Direct Link To This Post Posted: 22 May 2003 at 2:17pm

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

 

Back to Top
ljamal View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 16 April 2003
Status: Offline
Points: 888
Post Options Post Options   Thanks (0) Thanks(0)   Quote ljamal Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Nischint View Drop Down
Groupie
Groupie
Avatar

Joined: 26 August 2002
Location: India
Status: Offline
Points: 62
Post Options Post Options   Thanks (0) Thanks(0)   Quote Nischint Quote  Post ReplyReply Direct Link To This Post 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

Jam Like That
Back to Top
ljamal View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 16 April 2003
Status: Offline
Points: 888
Post Options Post Options   Thanks (0) Thanks(0)   Quote ljamal Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Nischint View Drop Down
Groupie
Groupie
Avatar

Joined: 26 August 2002
Location: India
Status: Offline
Points: 62
Post Options Post Options   Thanks (0) Thanks(0)   Quote Nischint Quote  Post ReplyReply Direct Link To This Post 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

Jam Like That
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.