Counting results with sum greater than a value

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rossmcd
Forum Newbie
Posts: 3
Joined: Mon Feb 27, 2006 8:15 pm

Counting results with sum greater than a value

Post by rossmcd »

Hi. Long time lurker, first time poster here.

I am building a topsites list, and in the process of ranking sites have stumbled on a problem. I list sites in order of the sum of their views in the last 7 days, from topsite_views which contains the number of views for any given day there is more than 1 view. I know how to do this.

My trouble is how to assign an actual rank number to the sites. I need to find how many sites are above them and add 1 (I know this much), but I don't know how to compare them when the actual calculation takes place inside the query.

Here is an example:

First, I find out the sum of views in last 7 days for the site in question ($valuex), then I want to see how many sites are better:

$qry = "select sum(views) as viewsum from topsite_views where viewsum >= '$valuex'";

Obviously, the query fails because it doesnt know what viewsum is until the query is finished... yada yada..

HAve you any suggestions on how to do this? Many thanks in advance!
kyoru
Forum Commoner
Posts: 26
Joined: Mon Feb 13, 2006 9:35 pm

Post by kyoru »

set them in ascending order?
rossmcd
Forum Newbie
Posts: 3
Joined: Mon Feb 27, 2006 8:15 pm

Post by rossmcd »

that's not what I am asking...
User avatar
Fractal
Forum Commoner
Posts: 54
Joined: Tue Aug 16, 2005 1:28 pm

Post by Fractal »

Well.. I suppose you could always

Code: Select all

$i = "1";
$rows = mysql_num_rows($query) or die(mysql_error();

while ($i <= $rows) {
  echo "#".$i." - blah<br />";
  $i++;
}
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Code: Select all

<?php
$number_to_show=7;
$result = mysql_query(
	sprintf(
		"
		SELECT
			SQL_CALC_FOUND_ROWS
			*
		FROM
			`sites`
		ORDER BY
			`whatever` ASC
		LIMIT
			%s
		",
		$number_to_show
	)
);
$result_count=mysql_query('SELECT FOUND_ROWS();');
$count = (int)mysql_result($result_count,0,0);
mysql_free_result($result_count);
$i=0;
while($row=mysql_fetch_assoc($result)) {
	//print_r($row);
	echo 'There are '.($count-$i).' entries after this one..';
	$i++;
}
mysql_free_result($result);
?>
Something along the lines of this would be a start, although I might have gotten my logic wrong this should outline the syntax
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you can use aggregate functions in the where clause from what I remember..
rossmcd
Forum Newbie
Posts: 3
Joined: Mon Feb 27, 2006 8:15 pm

Post by rossmcd »

what exactly is an aggregate function and how would I use them? Thanks for all these suggestions...
Post Reply