CODEWITHSUNDEEP
mysql
user1692342
user1692342

Reputation: 5237

Generating monthly report mysql

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

Upvotes: 0

Views: 117

Answers (1)

Nick
Nick

Reputation: 147166

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

Upvotes: 1

Related Questions