CODEWITHSUNDEEP
mysqlgroup-by
uncovery
uncovery

Reputation: 868

How to count the number of individual users in a table per day?

I have a log table that lists various actions of a system, including user logins. I want to find out how many unique users logged in during each day.

I tried this here:

SELECT date, count(username) 
FROM universal_log 
WHERE (plugin,action) 
IN (('system','login')) 
GROUP BY date 
ORDER BY date

which however gives me the amount of people who logged in, counting also all multiple logins by each user. I figured this is because I did not group by username. However if I do this here:

SELECT date, count(username) 
FROM universal_log 
WHERE (plugin,action) 
IN (('system','login')) 
GROUP BY date, username
ORDER BY date

I get a table with several entries per date instead of one, with the count how often each user logged in during that day.

How can I get a count +1 for each user when he logged in per day, no matter how often he logged in on that day?

Upvotes: 1

Views: 1952

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270607

Using a COUNT(DISTINCT username) should solve the duplications for you when applied to your first query. You were correct to GROUP BY date only, but just need to deduplicate the username values.

SELECT
  date,
  count(DISTINCT username) 
FROM universal_log 
WHERE (plugin,action) 
  IN (('system','login')) 
GROUP BY date 
ORDER BY date

When you added username to the GROUP BY in your second attempt, that had the effect of applying the COUNT() per date, per username, which is semantically different from what you want. For each column added to the GROUP BY, your aggregate will end up either producing more rows (applied over a wider data set) or in some cases the same rows (if the values were all distinct to begin with).

Upvotes: 4

Related Questions