1

This simple query (written in MySQL) is counting the number of WP posts published every day. But I need another information. I want to know the cumulative number of post for each date returned by the query.

If there are 100 posts published on this blog at date X I want the query to show 100. If I publish another post at date X + 1, I want to read X+1 => 101 posts

How can I achieve this objective?

SELECT DATE(post_date)    AS date, 
       concat(post_title) AS titles, 
       COUNT(*)           AS count
FROM   wp_posts 
WHERE  post_status = 'publish'
AND    post_parent = 0
AND    post_type = 'post'
GROUP  BY DATE(post_date)
ORDER  BY DATE(post_date)
2

If I understand you correctly, you want to have another field in your query which shows the total count for all posts, regardless of dates then. You could achieve this like so :

 SELECT DATE(post_date) AS date, 
           concat(post_title) titles, COUNT(*) AS count, 
           (select count(*) from wp_posts 
            WHERE post_status = 'publish'    
            AND post_parent = 0
            AND post_type = 'post') as CumulativeCount
    FROM  wp_posts 
    WHERE post_status = 'publish'
    AND post_parent = 0
    AND post_type = 'post'
    GROUP BY DATE(post_date)
    ORDER BY DATE(post_date)
|improve this answer|||||
1

You can calculate the running sum using a variable:

SET @runtot:=0;
SELECT sq.date, sq.titles, sq.count, (@runtot := @runtot + sq.count) AS rt
FROM 
(
    SELECT DATE(post_date) AS date, concat(post_title) titles, COUNT(*) AS count
    FROM  wp_posts 
    WHERE post_status = 'publish'
    AND post_parent = 0
    AND post_type = 'post'
    GROUP BY DATE(post_date)
    ORDER BY DATE(post_date)
) sq
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.