Query to find the data for every month of last year from the given date using mysql?
I want data for the the last 1 year from the given date For example:
I have date 2013-06-01
and I want data as follows also data I want is from three table using Group By
or something else
Month | Amount | Total_Data
June 2013 100 5
May 2013 80 4
- 100 5
- 100 5
开发者_StackOverflowJuly 2012 10 2
I try following query but didn't workout
SELECT DATE_FORMAT(rf.period, '%M %Y') as Month
, sum(p.amount * ((100-q.amount)/100)) as Amount
,count(distinct q.label_id) as Total Data
FROM table1 rf
, table2 p
, table3 q
,table4 a
where rf.period BETWEEN '2013-06-01' AND '2013-06-01'
and q.royalty_period BETWEEN '2013-06-01' AND '2013-06-01'
and a.id = q.album_id
and p.file_id = rf.id
and p.upc = a.upc
and p.local_revenue is not null
GROUP BY Month
You have identical dates in your BETWEEN clause: '2013-06-01' == '2013-06-01' so its only 1 day period
精彩评论