MySQL Subquery On Output Table

Question

I'm fairly new to subqueries/joins but I need to run a query on a table table that was generated from another query. I know this has probably been asked before, but I have not been able to find an answer that works form me.

Basic info about my queries:

query 1:

SELECT ipAddress,userId FROM loginAttempts WHERE browser = 'IE' GROUP BY userId

query 2:

SELECT COUNT(ipAddress) AS rows,ipAddress FROM loginAttempts GROUP BY ipAddress ORDER BY ipAddress;

Mainly I need to preform a search to only show unique values that reach certain criteria then show a count of how many of each unique values are returned from that.

This is a simplified version of my queries, but the loginAttempts table tracks each login attempt from each user, and I need to see how many login attempts were made from each IP address from each user. For example...

user1, 5 attempts from x.x.x.4
user1, 2 attempts from x.x.x.5
user2, 10 attempts from x.x.x.4
user2, 2 attempts from x.x.x.6
etc...

Thank you in advance for your help!

Use both columns in group by

devpro
– devpro

2015-12-20 07:13:30 +00:00
Commented Dec 20, 2015 at 7:13 — devpro
– devpro, Commented Dec 20, 2015 at 7:13

Drew · Accepted Answer · 2015-12-20 07:18:58Z

1

Try this:

Group by userid, ipAddress

SELECT COUNT(ipAddress) AS rows,ipAddress, userId 
FROM loginAttempts 
GROUP BY ipAddress, userId 
ORDER BY ipAddress

Together in group by. I hope this will help you

edited Dec 20, 2015 at 7:18

Drew

25k10 gold badges47 silver badges81 bronze badges

answered Dec 20, 2015 at 7:10

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

devpro Over a year ago

@drew: thanks for editing actually I m using mobile and I dont knw is there any option like Ctrl + K?

Drew Over a year ago

one time I scored 201 points on my phone in one day. A nightmare. Funny thing is, I haven't done that again even on a laptop. Sorry, I don't know the short-cut.

Collectives™ on Stack Overflow

MySQL Subquery On Output Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related