Jump to content

top 10 percentage problem


stelhar_1999

Recommended Posts

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?
Link to comment
Share on other sites

[!--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) [snapback]348660[/snapback][/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, [color=blue]SUM[/color](hits) [color=green]AS[/color] sumvotes GROUP BY page_id [color=green]ORDER BY[/color] sumvotes [color=green]DESC[/color] LIMIT 10;
[!--sql2--][/div][!--sql3--]

hope this helps.
Link to comment
Share on other sites

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..
Link to comment
Share on other sites

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

[u]id|radioname |blabla|....|blabla|hits[/u]
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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.