complex query with count and group by

chatguy2020 · #1 (**permalink**) 05-01-03, 10:27

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!

r937 · #2 (**permalink**) 05-01-03, 11:12

does informix allow derived tables in sql?

Code:

select login.userid
     , login.howmany as logins
     , coalesce(logout.howmany,0) as logouts
  from (
       select userid, count(*) as howmany
         from usertab 
        where action = 'LOGIN'
     group by userid
       ) login
left outer
  join (
       select userid, count(*) as howmany
         from usertab 
        where action = 'LOGOUT'
     group by userid
       ) logout
    on login.userid = logout.userid
 where login.howmany > coalesce(logout.howmany,0)

untested

rudy
http://r937.com/

Internet Services

Web Development

Digital Marketing

Consumer Tech