Jump to content

Archived

This topic is now archived and is closed to further replies.

stelhar_1999

top 10 percentage problem

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?

Share this post


Link to post
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.

Share this post


Link to post
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..

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

×

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.