I'm generally pretty self reliant on fudging something together that works but I have run into a brick wall on this one and am eventually reaching out for a nod in teh right direction..
my query:
$post_views = (int)$wpdb->get_var("
SELECT SUM(count) AS views
FROM ".$wpdb->prefix."post_views
WHERE id IN (".$post_id.") AND type = 0"
The database table looks like this :
id type period count
------- ------- ----------- -------
32310 0 20141023 8
32310 0 20141022 68
32310 1 201443 76
32310 2 201410 76
32310 3 2014 76
32310 4 total 76
The type 0 are the ones I'm interested in, I just want the sum of the COUNT column for the most recent 7 type 0 entries
I have been trying with things based around "ORDER BY period DESC LIMIT 7 " on the end of the query - to no avail, I generally get returns of 0 doing this.
a new type 0 row will be generated for each article every day, so thats why I need to only get the last 7
any help here would be massively appreciated, totally stuck for the first time ever with this.
SELECT SUM(count)
FROM (SELECT count
FROM wp_post_views
WHERE type = 0
AND id IN (684,42,7)
ORDER BY period DESC
LIMIT 7)
Or just determine the date a week ago first and use that to filter, but a subquery like this will work fine as well.