Jump to content


Photo

top 10 percentage problem


  • Please log in to reply
4 replies to this topic

#1 stelhar_1999

stelhar_1999
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 February 2006 - 12:31 PM

i want to make a top 10 using a field containing hits and show up a percentage in the results
What i try to do is to SUM the top 10 hits using a query like this:
"SELECT SUM(hits) as sumvotes ORDER BY sumvotes DESC LIMIT 10"
but The number that I get is the sum of all the hits in the db and not for the top 10
where am I wrong?

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 23 February 2006 - 01:10 PM

[!--quoteo(post=348660:date=Feb 23 2006, 07:31 AM:name=stelhar_1999)--][div class=\'quotetop\']QUOTE(stelhar_1999 @ Feb 23 2006, 07:31 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
i want to make a top 10 using a field containing hits and show up a percentage in the results
What i try to do is to SUM the top 10 hits using a query like this:
"SELECT SUM(hits) as sumvotes ORDER BY sumvotes DESC LIMIT 10"
but The number that I get is the sum of all the hits in the db and not for the top 10
where am I wrong?
[/quote]

well, to keep an accurate count, you're having to keep track of each hit and which page it is from, right? well, in your query, you have to distinguish between the hits by using a GROUP BY clause. so, if you have a column called "page_id" where you record the page that is hit, you would do something like this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] page_id, SUM(hits) AS sumvotes GROUP BY page_id ORDER BY sumvotes DESC LIMIT 10;
[!--sql2--][/div][!--sql3--]

hope this helps.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 stelhar_1999

stelhar_1999
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 01 March 2006 - 01:38 PM

every entry in the db is unigue so there is no need for a GROUP BY, I think that is!!!

#4 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 01 March 2006 - 02:05 PM

I think what you may need to do is use a loop for unique pages

$query=mysql_query("SELECT UNIQUE(page_id) FROM table");

while ($row = mysql_fetch_array($query)) {
$query2=mysql_query("SELECT count(hits) as sumhits FROM table WHERE page_id = '$row[page_id]'");
$row2=mysql_fetch_array($query2);
echo("$row[page_id] = $row2[sumhits]<br>");
}

That would get all of the pages and display how many hits they got each.. the only way i can see being able to get it to order the top 10 is to use a table with just page_id and hits as the coloumns and then truncate it before use and then insert all pages and hit counts then runa simple TOP query on it..
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#5 stelhar_1999

stelhar_1999
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 01 March 2006 - 02:49 PM

to be more precise here is the structure of my table "data"

id|radioname |blabla|....|blabla|hits
1 |testradio|blabla|....|blabla|200
-------------------------------------------
2 | testradio2|blabla|....|blabla|7

So every entry is unigue and I want to have the top 10 radios, showing their percentage instead of hits!
The problem is that I can't take the sum of the top 10 radios with a query!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users