0

Here is the query I have

select sender, count(*) as Total, date_format(senttime, '%m - %Y - %d') as Sent_Date 
from sendmail sm 
left join subuser su 
   on (sm.sender = su.sub_user) 
where senttime <= NOW() 
  AND senttime >= DATE_SUB(senttime, INTERVAL 7 DAY) 
group by date_format(senttime, '%d'), sender

Here is the actual output

enter image description here

It is showing only two dates 21 and 22 but I need 7 dates with total count 0.

How can I do this.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
Prabhakaran
  • 3,900
  • 15
  • 46
  • 113
  • 1
    While the screenshot of your output is helpful, what might be more helpful is sample data and desired output, preferably just listed in question or put into sqlfiddle.com – Hart CO Feb 22 '14 at 04:50

1 Answers1

0

Well if there is no sent time, it doesn't satisfy your where condition.

where senttime <= NOW() 
AND senttime >= DATE_SUB(senttime, INTERVAL 7 DAY)

You need to add an or to it:

where senttime IS NULL OR (senttime <= NOW() 
AND senttime >= DATE_SUB(senttime, INTERVAL 7 DAY))

And you may need to switch this

from sendmail sm 
left join subuser su

to

from subuser su 
left join sendmail sm

assuming sendmail only has values when something has been sent - you need to select from the one you know will be there, and then left join the one that you're not sure of (or do a different type of join)

dave
  • 62,300
  • 5
  • 72
  • 93