mysql - Generating monthly report mysql

Question

I have a table in which there are 5 columns,

id (auto incrementing number), titleId, version, created_at

A combination of titleId and version is always unique. I want to find out for each day for the past 1 month, how many unique titleIds were present along with the count of how many entries per day. This is because on a given day there might be multiple versions of the same titleId.

select count(*) from titles where  created_at >= '2019-08-12 00:00:00' and created_at <= '2019-08-13 00:00:00' will give me total number of titles which came on 12th August 

and

select count(distinct titleId) from titles where  created_at >= '2019-08-12 00:00:00' and created_at <= '2019-08-13 00:00:00'

will give me the count of unique titles on the 12th August. Is there a way for me to generate the data for the past 30/60 days?

I know I can run this command manually 30 times by changing the date to get the numbers, but was wondering if there is a better way to do this in mysql

标签： mysql

Answer 1

As long as there is an entry every day, this query should give you the data for each day for the last 30:

select date(created_at) as cdate, count(distinct titleId) as cnt
from titles
where created_at >= cur_date() - interval 30 day
group by cdate