WHERE clause inside COUNT(DISTINCT) clause

this is a question asked by Ryan Frank on forums.mysql.com, which i am facing as well.

I have the following in the beginning of my SELECT statement:

SELECT accounts.id, accounts.company,开发者_如何学C accounts.first, accounts.last,
COUNT(DISTINCT accounts_log.login_time) AS visits,
COUNT(DISTINCT accounts_log.ip_address) AS visitors,
COUNT(DISTINCT documents_log.access_time) AS docs,
MAX(accounts_log.login_time) AS login_time
FROM accounts

This returns all of the variables I need; however, I want to limit the variables that use COUNT(DISTINCT) to a date range. I can’t use the WHERE clause after the FROM clause. For example:

FROM accounts
WHERE accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to'

wouldn’t work because it wouldn’t give me ALL accounts like I need.

I’m looking for something like:

COUNT(DISTINCT accounts_log.login_time WHERE accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to') AS visits

P.S. I know the above doesn’t work and have run out of syntax options.

SELECT accounts.id, accounts.company, accounts.first, accounts.last,
COUNT(DISTINCT case when accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to' then accounts_log.login_time else null end) AS visits,
COUNT(DISTINCT case when accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to' then accounts_log.ip_address else null end) AS visitors,
COUNT(DISTINCT case when accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to' then documents_log.access_time else null end) AS docs,
MAX(accounts_log.login_time) AS login_time
FROM accounts

You can put condition in ON clause of LEFT JOIN :

 SELECT a.id, a.company, a.first, a.last,
 COUNT(DISTINCT al.login_time) AS visits
 FROM accounts a
 LEFT JOIN accounts_log al ON (al.account_id = a.id AND 
 al.login_time BETWEEN '$search_from' AND '$search_to')

And the same of other tables.