Mysql count current row count group by date

Question

I have two tables of users and articles and I want to count how many new users and how many new articles I have in the past 7 days.

tbl_users:

[Code, Username, Createdate]

1,David,01/01/2022
2,Henry,02/01/2022

tbl_articles:

[Code, Header, Createdate]

1,Hello,01/01/2022
2,Goodbye,02/01/2022

This query works now but it's slow and long. Please help me fix this query (I know it's bad) and if it's possible to add diff columns for both counters:

(Please go easy on me with the comments)

select articles.days_back,articles.count, users.count as users from (
select 0 as days_back,count(*) as count from tbl_articles where  date(createdate)< date_add(curdate(), interval -0 day)
union all 
select 1,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -1 day)
union all 
select 2,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -2 day)
union all 
select 3,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -3 day)
union all 
select 4,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -4 day)
union all 
select 5,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -5 day)
union all 
select 6,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -6 day)
union all 
select 7,count(*) from tbl_articles where  date(createdate)< date_add(curdate(), interval -7 day)
) as articles
left join 
(
select 0 as days_back,count(*) as count from tbl_users where  date(createdate)< date_add(curdate(), interval -0 day)
union all 
select 1,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -1 day)
union all 
select 2,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -2 day)
union all 
select 3,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -3 day)
union all 
select 4,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -4 day)
union all 
select 5,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -5 day)
union all 
select 6,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -6 day)
union all 
select 7,count(*) from tbl_users where  date(createdate)< date_add(curdate(), interval -7 day)
) as users
on articles.days_back=users.days_back

Your query is confusing and since it is not working, I would suggest that you remove it from your question. It would be helpful to see the important parts of your schema and an example how the result should look like. — Jens, Nov 29 '22 at 11:45
@Jens hi. It is working now, What makes you think it's not ? I added some sample. — user2396640, Nov 29 '22 at 12:05
If the statement is working, your explaination what you expect is wrong. You are counting all users and articles that have been created before today/yesterday/..., you can try it here http://sqlfiddle.com/#!9/622176/1/0. Do you want the count of all users / articles created on those specific days? Then we might need to also fix the query to get other results. If your query is correct, we only need to optimize to get the same result (eg. with a group by or something). — Jens, Nov 29 '22 at 17:53
Oh there could be a third interpretation as well, to query for the sums but up to 7 days (so you would end up adding an `AND date(createdate) > date_add(curdate(), interval +8 day)` to each line or something. — Jens, Nov 29 '22 at 17:55
@Jens thanks for the sqlfiddle, I wasn't aware. Yes I want to count how many created not IN THIS DAY but UNTIL THIS DAY. my query works but it's slow and reparative, Imagin if I wanted to do it for 14 and not 7 days or 3 tables instead of 2 it would be very very long. — user2396640, Nov 29 '22 at 18:26

score 0 · Answer 1 · answered Nov 29 '22 at 12:17

0

Something like that?

SELECT Counter.Articles, Counter.Users (
SELECT COUNT(1) FROM tbl_articles WHERE DATE(createdate) BETWEEN (CURRENT_TIMESTAMP() AND DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)) Articles,
SELECT COUNT(1) FROM tbl_users WHERE DATE(createdate) BETWEEN (CURRENT_TIMESTAMP() AND DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)) Users
) Counter;

haven't tested it btw

answered Nov 29 '22 at 12:17

Dxg125

51
1
7

It's hard to know what you meant because it's not working. But I don't see how this query will group it per each day in the past 7 days – user2396640 Nov 29 '22 at 13:40
I'll return the favor to you, you have never written this out ;) try this then if you still havent done it; SELECT articles.Date, COUNT(1) FROM articles WHERE DATE(articles.Date) <= DATE(CURRENT_TIMESTAMP()) AND DATE(articles.Date) >= DATE(DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)) GROUP BY articles.Date – Dxg125 Nov 30 '22 at 14:18
The question is still wrong, I'll edit it according to the discussion in the question comments. – Jens Nov 30 '22 at 18:39
@Dxg125 this query results how many created at that date, not till this date (SELECT date(tbl_articles.createdate) , COUNT(1) FROM tbl_articles WHERE DATE(tbl_articles.createdate) <= DATE(CURRENT_TIMESTAMP()) AND DATE(tbl_articles.createdate) >= DATE(DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)) GROUP BY date(tbl_articles.createdate)) – user2396640 Nov 30 '22 at 21:20

score 0 · Answer 2 · answered Nov 30 '22 at 18:33

It is not as easy as just use a group by, but as you thought group by is important to do this. I guess there will be no simple 5 line query to get your expected result. I would suggest to do multiple queries and some backend code instead of a single query. It is hard to read, understand and maintain.

But it is possible.

A GROUP BY DATE_FORMAT(Created,'%Y-%m-%d') in a simple select would already give you the count per day.

The next problem to solve is, that you want the days_back instead of the date itself. Thats also quite simple with DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), DATE_FORMAT(Createdate,'%Y-%m-%d')).

But the third requirement is the difficult. You don't want the amount per day but the sum up to that day, for a variable amount of days back. Unfortunatly, mysql does not offer a Sequence (like SELECT numbers from 1 to X) to join with the other parts of the query. If you can live with a restriction to somewhat about 300 years, https://stackoverflow.com/a/9296238/4675841 will help there. To get article counts and user counts together, you coult either use union (all) or a join, I used the join and came up with this query.

SELECT user_query.diff as days_back, article_count as count, user_count as users
FROM (
    SELECT DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), gen_date) as diff, COUNT(create_date) as user_count
    FROM (
        SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date
        FROM
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) possible_dates
        LEFT JOIN (SELECT DATE_FORMAT(Createdate,'%Y-%m-%d') as create_date FROM tbl_users) counts
            ON create_date <= gen_date
        WHERE gen_date BETWEEN DATE_SUB(NOW(), INTERVAL 20 DAY) AND NOW()
        GROUP BY gen_date) user_query
INNER JOIN (
    SELECT DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), gen_date) as diff, COUNT(create_date) as article_count
    FROM (
        SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date
        FROM
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
        LEFT JOIN (SELECT DATE_FORMAT(Createdate,'%Y-%m-%d') as create_date FROM tbl_articles) counts
            ON create_date <= gen_date
        WHERE gen_date BETWEEN DATE_SUB(NOW(), INTERVAL 20 DAY) AND NOW()
    GROUP BY gen_date) article_query
    ON user_query.diff = article_query.diff
ORDER BY days_back

Try it out.

Just to make sure I was understood correctly, the range is not from a specific date until last 0-7 but it's since the beginning of time until last 0-7. Your (thank you) reply gives me a more dynamic option just to change the interval (20 to 7 i.e.) but it's still very long and apparently less efficient , 5.7 seconds to load compare to 3.8 seconds to mine (200k+) records — user2396640, Nov 30 '22 at 21:34
Oh sorry I thought I read that you also want to be able to change the "7" to something else so I wanted to get that part dynamic as well (you could use a parameter for the 20 or remove the WHERE at all to get everything from 1970 to today). For performance, you could try doing a union to combine both the article and the user query instead of the join. However, I still recomment to have a good architecture, have parts of the logic in the frontend, the backend and in the database instead of implementing "frontend requirements" directly in the database. — Jens, Nov 30 '22 at 22:45

Mysql count current row count group by date

2 Answers2