Page 1 of 1

Counting results with sum greater than a value

Posted: Mon Feb 27, 2006 8:42 pm
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!

Posted: Mon Feb 27, 2006 8:55 pm
by kyoru
set them in ascending order?

Posted: Mon Feb 27, 2006 9:19 pm
by rossmcd
that's not what I am asking...

Posted: Mon Feb 27, 2006 9:24 pm
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++;
}

Posted: Mon Feb 27, 2006 11:16 pm
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

Posted: Mon Feb 27, 2006 11:18 pm
by feyd
you can use aggregate functions in the where clause from what I remember..

Posted: Tue Feb 28, 2006 5:16 am
by rossmcd
what exactly is an aggregate function and how would I use them? Thanks for all these suggestions...