Reputation: 21
I am using a Firebird database, but answers using SQL syntax of most other database engines are also acceptable.
I have two tables:
WORKER
workerid: integer
worker names and so ot.
LOGEVENT
logeventid: integer
logday: integer
workerid: integer
Thus LOGEVENT table is used to track worker logins. And for simplicity here I use logday as integer which is represent login day. For example, it could be a day of year from 1 to 365.
Therefore the typical log table may look like that:
logeventid logday workerid
1 5 3
2 5 4
3 5 3
4 5 7
5 6 4
6 6 3
7 6 4
As seen the worker with id = 3 did two logins on 5th day.
Now I need to create a SQL query producing total number of "unique" logins per day (several logins by the same worker during the same day should be counted as one event).
The following query:
select count(logeventid) as logincount, logday from logevent
group by logday
order by logday
Will show logincount = 4 for 5th day. Because it counted worker with id = 3 twice. While I must have to count only distinct workers logins. So, I need a query producing only 3 logins for the 5th day.
Upvotes: 2
Views: 8270
Reputation: 86
You want to count the distinct workerids per day. so for that you need this query:
select count(DISTINCT workerid) as logincount, logday from logevent
group by logday
order by logday
Upvotes: 4
Reputation: 94914
You want to count distinct users per day:
select logday, count(distinct workerid) as logincount
from logevent
group by logday
order by logday;
Upvotes: 5
Reputation: 258
select count(logeventid) as logincount, logday from logevent
group by logday, workerid
order by logday
Extra group by on workerid should eliminate the double logins.
Upvotes: 1