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:
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.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
Thanks for any help.