Hi Ian,
Why do you think something's wrong? Here is my test data and the results
of your query:
---
mysql> SELECT * FROM wp_views;
+---------+---------+------------+-------+
| blog_id | post_id | date | views |
+---------+---------+------------+-------+
| 1 | 1 | 2009-12-16 | 2 |
| 1 | 1 | 2009-12-17 | 3 |
| 1 | 2 | 2009-12-16 | 4 |
| 1 | 2 | 2009-12-17 | 5 |
| 2 | 1 | 2009-12-16 | 6 |
| 2 | 1 | 2009-12-17 | 7 |
| 2 | 2 | 2009-12-16 | 8 |
| 2 | 2 | 2009-12-17 | 9 |
| 1 | 1 | 2009-12-18 | 1 |
| 1 | 2 | 2009-12-18 | 1 |
| 2 | 1 | 2009-12-18 | 1 |
| 2 | 2 | 2009-12-18 | 1 |
+---------+---------+------------+-------+
12 rows in set (0.00 sec)
mysql> SELECT blog_id, post_id, sum( views ) AS views FROM wp_views
WHERE (date <= "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id,
post_id ORDER BY views DESC LIMIT 10;
+---------+---------+-------+
| blog_id | post_id | views |
+---------+---------+-------+
| 2 | 2 | 17 |
| 2 | 1 | 13 |
| 1 | 2 | 9 |
| 1 | 1 | 5 |
+---------+---------+-------+
4 rows in set (0.00 sec)
---
Seems OK to me... Are you getting different results?
Take care,
Aleksandar
Ian wrote:
Hi,
I am sure there is a simple solution to this problem, I just cant find it :)
I have got a table that records views for an article for each blog per day.
So the structure is as follows:
CREATE TABLE `wp_views` (
`blog_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`date` date NOT NULL,
`views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Now thats fine and I can pull top blogs per day and thats all fine, but what
I am after is pulling the top articles for a time period and where I am
running into problems is where two blogs have the same post_id's the views
get sum()'d for the day and I cant figure out (read end of year mind block)
how to get around it. Here is my current query (for last 7 days):
SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date <=
"2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id ORDER BY
views DESC LIMIT 10
Any ideas as to whats wrong. I know its something simple, I just cant put my
finger on it.
Thanks in advance,
Ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
