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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
