CODEWITHSUNDEEP
mysqljoingroup-by
Ian
Ian

Reputation: 12241

Mysql Join query using a group by

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

  1. Each distinct user id in the accesslog table.

  2. A count of how many distinct ip addresses each userid has accessed the site with, also from the accesslog table.

  3. 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?

Upvotes: 0

Views: 10758

Answers (1)

ChssPly76
ChssPly76

Reputation: 100706

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

Upvotes: 4

Related Questions