i want to show complete list of last 30 days here is my query:
select Distinct DATE_FORMAT(transactions.transaction_date,'%c-%d-%Y') as transaction_date,sum(amount)as Amount from transactions group by DATE_FORMAT(transactions.transaction_date,'%c-%d-%Y')
and here is my query result:
but i want
transaction_date Amount
1-01-2014 0
2-01-2014 0
upto so on how i get the complete result?
The typical approach here is to first write a query that gets the list of dates you want, then use an outer join to associate the transaction amounts for the dates in that range on which there were transactions.
My recommendation is to install common_schema and use the common_schema.numbers table to generate the date list.
For example you can run a query like this to get the last 30 days (exclusive of today):
select (current_date() - interval n day) as day
from common_schema.numbers
where n between 1 and 30
order by day
Then you can combine that with your existing query to get the desired result (I made some other small changes to your query to limit it to the relevant date range and to use DATE() instead of DATE_FORMAT() for the sake of simplicity):
select days.day, coalesce(transactions_rollup.total,0)
from
(
select (current_date() - interval n day) as day
from common_schema.numbers
where n between 1 and 30
) days
left outer join (
select date(transaction_date) as day, sum(amount) as total
from transactions
where transaction_date >= current_date() - interval 30 day
and transation_date < current_date()
group by date(transaction_date)
) transactions_rollup on transactions_rollup.day = days.day
order by days.day
What you need is a generator view (similar to the one obtainable by PostgreSQL generate_series() function) containing all the dates of the month without gaps. Unfortunately MySQL does not have such a function, so you need to either pregenerate the dates in some calendar table and then left join your original query to it, or rely on some big enough table for it.
