CODEWITHSUNDEEP
mysqlsql
Sarah
Sarah

Reputation: 11

Using a MySQL select query to generate a report

I have two tables. I need to produce two quick reports on the number of logins per user per day and the number of logins per user per IP address. How can I generate these reports?

Users
----
user_id
username

UserLoginLog
----
log_id
user_id
logdate
ipaddr

Upvotes: 0

Views: 496

Answers (1)

Mureinik
Mureinik

Reputation: 311163

The syntax you're looking for is group by - it allows you to break a dataset up to groups, and run an aggregate function on each of them.

Logins per day:

SELECT   DATE(logdate), COUNT(*)
FROM     UserLoginLog
GROUP BY DATE(logdate)

Logins per user per IP address:

SELECT   user_id, ip_addr, COUNT(*)
FROM     UserLoginLog
GROUP BY user_id, ip_addr

Upvotes: 2

Related Questions