[Solved] What is the error in my query?

by


In your query, you specify

where (MONTH(abc.email_date)=MONTH(NOW()) AND YEAR(abc.email_date)=YEAR(NOW()))

But in the subquery (the one returning 18 results), you have 6 emails with a month that is not december 2014. There’s no way that those emails can be returned by a query that explicitly excludes them.

You want those emails as well so you get 18 results ? Remove the WHERE clause excluding them:

SELECT Count(abc.id) AS total_this_month,
       t.*
FROM   email_details abc
       JOIN (SELECT Count(email_details.id)         AS total_emails,
                    Max(`email_details`.email_date) AS email_date1,
                    `email_details`.*
             FROM   (`cld_users`
                     JOIN email_details
                       ON email_details.fk_user_id = cld_users.id)
             GROUP  BY `email_details`.`email_title`
             ORDER  BY `email_details`.`email_date` DESC) AS t
         ON abc.email_title = t.email_title
GROUP  BY t.email_title
ORDER  BY t.`email_date` DESC

Starting from there, if you want to count the emails from the current month, simply replace:

SELECT Count(abc.id) AS total_this_month,

with

SELECT SUM(CASE WHEN MONTH(abc.email_date)=MONTH(NOW()) AND YEAR(abc.email_date)=YEAR(NOW()) THEN 1 ELSE 0 END) AS total_this_month,

3

solved What is the error in my query?