complex query with count and group by

Hey I have a table with the following fields:

userid
action

now, action include login and logout. Here in this table there will be multiple userids with multiple actions. Is there a way that we can retrieve the user ids whose Logins are greater than logouts?

I wrote this:

select distinct(userid), action, count(*) from usertab where action in("LOGIN", "LOGOUT") group by userid, action order by userid desc

A snippet of result is:

ROBL LOGIN 2
ROBL LOGOUT 2
RIOS LOGIN 4
RIOS LOGOUT 4
PRATI LOGIN 43
PRATI LOGOUT 37

Now I need to extract PRATI only as that user has more logins than logouts. That way, I can get only users whose logins are more than logouts. I tried using count and having but I didn't get the result.

Is there any way to do it without views?

Any answers would be appreciated.

Thanks!

Not exactly sure what DB you're using, but here's how I'd do it in Postgres:

SELECT login.userid FROM (SELECT userid, count(*) as logincount FROM usertab WHERE action = 'LOGIN' GROUP BY userid) as login,
(SELECT userid, count(*) as logoutcount FROM usertab WHERE action = 'LOGOUT' GROUP BY userid) as logout WHERE login.userid = logout.userid AND login.logincount > logout.logoutcount

I think that should do it. Maybe someone can come up with something a bit cleaner.

-b

try something like this (no guarantees on syntax)...

select result.userid
from (select userid as login_userid,count(*) as login_count
from usertab
where action = 'login'
group by userid) login_result
inner join
(select userid as logout_userid,count(*) as logout_count
from usertab
where action = 'logout'
group by userid) logout_result
on logout_result.userid = login_result.userid
where logout_result.logout_count <>
login_result.login_count
and login_result.login_count <>
logout_result.logout_count + 1; /* user may be logged in */

HTH

v