List: General Discussion « Previous MessageNext Message » From: Chris W Date: March 18 2010 6:27pm Subject: Re: 7-day average View as plain text
```I changed the names slightly when I tested it but here is the query that
gives the result you want...

SELECT a.`AccountID`, a.`company`, sum(h.hits), count(h.AccountID),
sum(h.hits)/count(h.AccountID) AS AvgHits
FROM account a
JOIN hitsperday h using (AccountID)
WHERE `date` < DATE(now()) AND `date` >= DATE(DATE_SUB(now(), INTERVAL 7
DAY))
GROUP BY a.`AccountID`, a.`company`
ORDER BY sum(h.hits)/count(h.AccountID) DESC

I think I would change the math.  Since there are several days in there
where there are no hits, that should in my opinion count against the
average.  So instead of dividing by count(h.AccountID),  I would divide
by 7 which would change the values to about...

11.4
8.6
5.7

still in the same order though.

Brian Dunning wrote:
> My brain just squirted out my ear trying to figure this out, so maybe one of you can
> help me scoop it back up.
>
> I have a table of accounts, and a table of hits-per-day per account. I'm trying to
> sort my accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago
> through yesterday).
>
> Table: accounts
> +------------+---------+
> | account_id | company |
> +------------+---------+
> |          1 | ABC     |
> |          2 | DEF     |
> |          3 | GHI     |
> +------------+---------+
>
> Table: hits_per_day
> +------------+------------+------+
> | account_id |    date    | hits |
> +------------+------------+------+
> |          1 | 2010-03-01 |   10 |
> |          1 | 2010-03-12 |   10 |
> |          2 | 2010-03-12 |   10 |
> |          3 | 2010-03-12 |   10 |
> |          1 | 2010-03-15 |   30 |
> |          2 | 2010-03-15 |   70 |
> |          3 | 2010-03-15 |   50 |
> |          3 | 2010-03-18 |   10 |
> +------------+------------+------+
> * Notice the first and last rows should be excluded because they are not between 8
> days ago and yesterday.
>
> So the results should look like this:
> +------------+---------+----------+
> | account_id | company | avg_hits |
> +------------+---------+----------+
> |          2 | DEF     |       40 |
> |          3 | GHI     |       30 |
> |          1 | ABC     |       20 |
> +------------+---------+----------+
>
> I hope that makes sense! I've driven myself crazy with this to the point that I can
> no longer think about it clearly. Thanks for any help.
>
>
>
>
```