MYSQL Query: output the count of insertions in the Log table

Question

I've got 2 tables:

----Users-----

id (int)
username (varchar)

----Log-----

id (int)
user_id (int)
logged (datetime)

Every time the user logs in, a record is inserted into the Log table. I would like to create a statement that will output:

EXAMPLE:

Jason -> 96
Mary -> 54
Jack -> 53

I've been pulling my hair out with this SQL statement required to do this. Any insight?

More info please. What is "96"? How many times should each user appear in the output? — Larry Lustig
– Larry Lustig, Commented Jan 5, 2011 at 18:30

Tasawer Khan · Accepted Answer · 2011-01-05 18:35:54Z

1

Select Users.username , Count (Log.user_id)
From Users, Log
WHERE Users.id=Log.user_id
Group by log.user_id

answered Jan 5, 2011 at 18:35

Tasawer Khan

6,1787 gold badges49 silver badges69 bronze badges

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

2 Comments

Tasawer Khan Over a year ago

You are welcome . you can vote the question up and mark it as answer if it helps :)

regilero Over a year ago

dangerous group by, in standard SQL you should have the Users.username in the GROUP BY, this extended group by syntax on MySQl is dangerous, should be avoided for beginners. I would even add the User.id in the Group by and select to avoid duplicate names if username is not unique.

Nick Rolando · Accepted Answer · 2011-01-05 18:42:13Z

0

I'm going to make an assumption from the small amount of info given. I think your best bet would be something like:

select u.username, ' -> ', l.user_id from Users u, Log l where l.user_id = u.id

answered Jan 5, 2011 at 18:42

Nick Rolando

26.2k13 gold badges84 silver badges120 bronze badges

Collectives™ on Stack Overflow

MYSQL Query: output the count of insertions in the Log table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related