开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜