WHERE clause inside COUNT(DISTINCT) clause

Question

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, 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.

Answer 1

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