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!

---

Clarification of Question by rossmcd-ga on 28 Feb 2006 13:27 PST

To clarify:

The query needs to count the number of sites in the toplist_views
table that have a sum of views column [where date <= curdate() and
date >= DATE_SUB(curdate(),INTERVAL 7 day)] which is greater than a
specific value. I cannot use subselects or subqueries because of my
mysql version.

For example, if I was just counting the sum of views for one site it would be:

$prerankqry = "select sum(views) as viewsum from toplist_views where
siteid='$siteid' and date <= curdate() and date >=
DATE_SUB(curdate(),INTERVAL 7 day) group by siteid";