CODEWITHSUNDEEP
mysqlsqldatabasedatecount
user1563414
user1563414

Reputation: 91

mysql: getting a count of all the data, daily, for the last 365 days

every day, a table in my mysql db(db.table1) gets data written to it. It gets anywhere between 9000 to 30000 rows per day. This has been happening daily for the last 3 years. is there a way (perhaps some kind of count(*) command) to get a count of how many rows have been written to the db every day for the last 365 days.

EDIT: I have a FirstCreated date that looks like this(2011-08-02 15:01:21) for every entry

It would look something like this:

> 2012-01-01 | 3998 
> 2012-01-02 | 8798 
> 2012-01-03 | 12000 
> 2012-01-04 | 398 
> 2012-01-05 | 2876


until

2012-12-31 | 5788

Thanks very much!

Upvotes: 1

Views: 1299

Answers (2)

Code Lღver
Code Lღver

Reputation: 15603

Select count(*) as totalRows, DATE_FORMATE("%Y-%m-%d",FirstCreated) as firstDate 
    from table_name 
          Where DATE_FORMATE("%Y-%m-%d",FirstCreated) IN ("2012-01-01" AND "2012-12-31")
          group by FirstCreated;

Use the above query.

Upvotes: 1

Randy
Randy

Reputation: 16677

probably.

you have not given your database structure.

IF there is a date field on each record, like INSERT_DT or something, then write a SQL statement that uses a GROUP BY on that date and then use an AGGREGATE FUNCTION like COUNT to count up the rows.

Upvotes: 1

Related Questions