Counting results with sum greater than a value
Posted: Mon Feb 27, 2006 8:42 pm
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!
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!