Re: SELECT Records using multiple ciritria

[Peter Brawley]

One way ...

select 
  sum7  / if(n7=0,1,n7) as avg7,
  sum14 / if(n14=0,1,n14) as avg14,
  sum28 / if(n28=0,1,n28) as avg28
from (
  select 
    sum( if(datediff(curdate(),date)<=7,value,0) ) as sum7,
    sum( if(datediff(curdate(),date)<=7,1,0) ) as n7,
    sum( if(datediff(curdate(),date)<=14,value,0) ) as sum14,
    sum( if(datediff(curdate(),date)<=14,1,0) ) as n14,
    sum( if(datediff(curdate(),date)<=28,value,0) ) as sum28,
    sum( if(datediff(curdate(),date)<=28,1,0) ) as n28
  from tbl
) x;

but first you'll need to clean up your date data---MySQL does not use the weird US date format, you'll need to change that column to MySQL dates, yyyy-mm-dd.