Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jun 2005
    Posts
    42
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Angry Help with a JOIN

    Hi folks,

    I spent a while working on this seemingly simple SQL query and I don't have enough experience to solve this one...
    I have a table with data like this:

    memberid time username hostname domain result
    1000 11111111111 Bob test google.com 1
    1000 22222222222 Bob test hotmail.com 0
    1000 77777777777 Bob test hotmail.com 1
    1000 55555555555 Alice test2 google.com 1
    1000 22222222222 Alice test2 gmail.com 0

    What I am trying to get is a table that looks like this...

    domain success_count blocked_count last_time last_user
    google.com 2 0 55555555555 Alice
    hotmail.com 1 1 77777777777 Bob
    gmail.com 0 1 22222222222 Alice

    With this query, I can get most of what I want:

    Code:
    SELECT domain, COUNT(CASE WHEN result > 0 THEN 1 END) as success, COUNT(CASE WHEN result = 0 THEN 1 END) as blocked, MAX(time) FROM domains GROUP BY domain ORDER BY success DESC LIMIT 100
    The last part I am missing is the last user. I somehow need to do a join (I think) on this, but after hours of trial and error I have given up and hope that someone here can help me.

    Thanks for any help.
    Last edited by fbelzile; 06-21-2014 at 03:47 PM. Reason: Solved

  • #2
    New Coder
    Join Date
    Jun 2005
    Posts
    42
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I think I finally solved it. Here is my solution for anyone with a similar problem:

    Code:
    select domain, t2.successMax, t2.blockedMax, time, username
    from domains t1
    inner join
    (
      select domain as maxDomain, COUNT(CASE WHEN result > 0 THEN 1 END) as successMax, COUNT(CASE WHEN result = 0 THEN 1 END) as blockedMax, max(time) maxTime
      from domains group by domain
    ) t2
      on t1.domain = t2.maxDomain and t1.time = t2.maxTime
    GROUP BY domain ORDER BY t2.successMax DESC LIMIT 100;


  •  

    Posting Permissions

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