mysql - how to show list of dates of previous month? -

i want show complete list of last 30 days here query:

 select distinct date_format(transactions.transaction_date,'%c-%d-%y') transaction_date,sum(amount)as amount transactions group  date_format(transactions.transaction_date,'%c-%d-%y') 

and here query result:

but want

transaction_date amount 1-01-2014         0 2-01-2014         0 

upto on how complete result?

the typical approach here first write query gets list of dates want, use outer join associate transaction amounts dates in range on there transactions.

my recommendation install common_schema , use common_schema.numbers table generate date list.

for example can run query last 30 days (exclusive of today):

select (current_date() - interval n day) day common_schema.numbers n between 1 , 30 order day 

then can combine existing query desired result (i made other small changes query limit relevant date range , use date() instead of date_format() sake of simplicity):

select days.day, coalesce(transactions_rollup.total,0) (   select (current_date() - interval n day) day   common_schema.numbers   n between 1 , 30 ) days left outer join (   select date(transaction_date) day, sum(amount) total   transactions    transaction_date >= current_date() - interval 30 day   , transation_date < current_date()   group date(transaction_date) ) transactions_rollup on transactions_rollup.day = days.day order days.day