CODEWITHSUNDEEP
mysqlmariadb
nland
nland

Reputation: 669

Count DISTINCT on a single column over multiple conditions

I have a table, and I want to get the DISTINCT count of usernames over a certain period of time. Currently I'm running this query 

SELECT DISTINCT username FROM user_activity WHERE company_id = 9 AND timestamp BETWEEN  '2015-09-00' AND '2015-10-01' AND action = "Login Success";

It works great, however, I have multiple Companies that I want to select the count for. How do I expand the previous query to show me the distinct counts for multiple companies?

select count(distinct username),
    sum(case when company_id = 1 then 1 else 0 end) A,
    sum(case when company_id = 9 then 1 else 0 end) B
from `user_activity` Where timestamp BETWEEN  '2015-09-00' AND '2015-10-01' AND action = "Login Success"

I've done something like this, however, I'm not getting the correct numbers. Ideally I would like to list each count as a different value for ease of reading, like the previous query illustrates. I don't need the count(distinct username) column to appear in my result, just the conditionals.

Thanks in advance.

Upvotes: 0

Views: 215

Answers (1)

Arth
Arth

Reputation: 13110

If you don't mind two rows instead of two columns:

  SELECT company_id, COUNT(DISTINCT username)
    FROM user_activity
   WHERE company_id IN (1,9) 
     AND timestamp >= '2015-09-01'
     AND timestamp <  '2015-09-01' + INTERVAL 1 MONTH
     AND action = "Login Success"
GROUP BY company_id

Upvotes: 2

Related Questions