Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    58

    Unanswered: 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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •