CODEWITHSUNDEEP
phpmysql
rkaartikeyan
rkaartikeyan

Reputation: 2007

How to get Date wise Report from MySql

I have table(dataTbl) like bellow.

id  date
 1  05-08-2013
 2  10-08-2013
 3  11-08-2013
 4  12-08-2013
 5  12-08-2013
 6  12-08-2013
 7  21-08-2013
 8  22-08-2013
 9  23-08-2013
10  23-08-2013
11  24-08-2013
12  29-08-2013
13  30-08-2013
14  30-08-2013
15  30-08-2013

I want to get output like bellow between 01-08-2013 to 30-08-2013 after i execute query:-

date       entries
01-08-2013 0
02-08-2013 0
03-08-2013 0
04-08-2013 0
05-08-2013 1
06-08-2013 0
07-08-2013 0
08-08-2013 0
09-08-2013 0
10-08-2013 1
11-08-2013 1
12-08-2013 3
13-08-2013 0
14-08-2013 0
15-08-2013 0
etc., etc.,

How to do this in MySQL query? Please help me!

In the table its having records for 5th, 10th, 11th, 12th(3 entries), 21st, 22nd, 23(2 entries).

But the output should starts with 01, 02, 03, 04, 05, 06, 07, 08, 09, 10... with number of entries.

Upvotes: 1

Views: 2117

Answers (1)

skv
skv

Reputation: 1803

If you do not want to use php or stored procedures, you will need a second table to list all dates once you have that as say all_dates

select listdate, count(datatbl.date) from all_dates 
left outer join datatbl on all_dates.listdate = datatbl.date

Or alternatively 

Select listdate,0 from all_dates where listdate not in (select distinct date from datatbl)
union 
select date, count(*) from databl group by date

Upvotes: 2

Related Questions