Simple Query Question

--0016e6d7dff511c59f047aeb5cf9
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d7dff511c59f047aeb5cf9--

Re: Simple Query Question

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=gcdmg-mysql-2@m.gmane.org

Re: Simple Query Question

--0016e6d58b2f8abbaa047aebb9f3
Content-Type: text/plain; charset=ISO-8859-1

Hi,

Thanks, I just checked and it was a memcache that was caching the output.
See I knew it was a simple solution ;)

Thanks for the effort everyone and sorry for wasting time.

Regards
Ian

2009/12/17 Aleksandar Bradaric

> 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
>>
>>
>

--0016e6d58b2f8abbaa047aebb9f3--