MySQL Query – How to Count Rows by Date

MySQL

I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table.

I have used the following query to get the data of single day, 

select count (*) from tx_feeder 
where datum between (select sysdate -31 from dual) and (select sysdate -1 from dual); 

SQL> 3455890

It will give the count for the whole month. But instead of getting the count of the entire month as a whole, I want to get the output like following way, total count of each day in a month from a single SQL query.

     sysdate -31 = 11190  
     sysdate -30 = 13390  
     sysdate -29 = 65790  
     sysdate -28 = 44390   
     ........  
     sysdate -1 = 14590

Best Answer

That SQL dialect is from Oracle but this answer will be for MySQL.

If datum is DATE

select count (*),datum date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum;

If datum is DATETIME or TIMESTAMP

select count (*),DATE(datum) date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum);

If you are looking for a grand total to be included, do WITH ROLLUP

select count (*),datum date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum WITH ROLLUP;

or

select count (*),DATE(datum) date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum) WITH ROLLUP;
Related Question