Roman Vasin
Roman Vasin

Reputation: 21

How to use group by and select distinct

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

Answers (3)

p130ter
p130ter

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

Thorsten Kettner
Thorsten Kettner

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

A. van Esveld
A. van Esveld

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

Related Questions