Mysql Join query using a group by

Question

I'm trying to write a query that will return ...

Each distinct user id in the accesslog table.
A count of how many distinct ip addresses each userid has accessed the site with, also from the accesslog table.
That userid's name, email address, and company name, from the users table.

I've got 1 and 2, but I'm not sure how to get 3. I suck at joins, I admit. :(

Here's the query I have now:

SELECT DISTINCT userid, COUNT(ipaddress) AS c FROM accesslog WHERE url LIKE 'www.example.com%' and userid != '' GROUP BY userid ORDER BY c desc

How would I JOIN in the other pieces of data that I want?

Ah cr@p... just remembered that my users table and accesslog table are in different databases, and I don't have access to a root mysql user. So I can't do this query after all.. :( — Ian
– Ian, Commented Aug 25, 2009 at 23:58

ChssPly76 · Accepted Answer · 2009-08-26 00:02:36Z

4

You'll need to join to users table and add all its columns to GROUP BY:

SELECT a.userid, u.name, u.email, u.company, COUNT(a.ipaddress) AS c
  FROM accesslog a, USERS u
 WHERE a.userid = u.userid
   AND a.url LIKE 'www.example.com%'
   AND a.userid != ''
GROUP BY a.userid
ORDER BY c desc

edited Aug 26, 2009 at 0:02

answered Aug 25, 2009 at 23:56

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

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

3 Comments

BoltBait Over a year ago

I don't think you'll need 'DISTINCT' as the 'group by' should take care of that.

Ian Over a year ago

thanks for your answer. it would have worked if my databases weren't so screwy.

ChssPly76 Over a year ago

@BoltBait - you're right; I've just copied / edited Ian's query and forgot to remove it. Will edit.

Collectives™ on Stack Overflow

Mysql Join query using a group by

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related