Mysql – How to get a column of the select to be “cumulative” (like in excel)

MySQL

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)

Best Answer

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)

Related Solutions

Mysql – How to get a column A which has the smaller column B grouping by column C

Oracle solution to both problems:

SELECT ID, Category FROM (
   SELECT FIRST_VALUE(ID) OVER 
      (PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
      , Category, ID FROM DATAS
   )
   WHERE ID = IDOfSmallestOrdForCategory;

Generic solution to both problems:

SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;

Oracle DDL/DML:

CREATE TABLE DATAS (
    ID       Integer,
    CATEGORY Integer,
    ORD      Integer 
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Best Answer

Related Solutions

Mysql – How to get a column A which has the smaller column B grouping by column C

Mysql – Help optimizing MySQL slow query

Related Question