Getting results from one table, grouped by user and date, joined on the user's name and account info from another table?

Question

Let's say I have three tables (with the columns in each):

USERS: id, name, account_id

ACCOUNTS: id, name

EVENTS: event_id, user_id, date

And I want to get the total count of events from EVENTS for each user, within a date range, along with the user's name and account name.

I can use GROUP BY to group the results by user_id, but how do I then join that to the users and accounts to get that info in each row? I'm trying to get an output like:

+-------+---------------------+--------+

| name  | account_name        |count(*)|

+-------+---------------------+--------+

| Joe   | XYZ, Inc.           |     10 |

| Bob   | Vandalay Industries |     21 |

| Mary  | Account Name Here   |     32 |

+-------+---------------------+--------+

where the third column is the total number of events in the EVENTS table for that user_id in a specified date range.

Sorry, I can never get the hang of joins like this..

Answer 1

Assuming that you have an id column on users and accounts tables

SELECT 
 users.name,
 accounts.name,
 count(events.event_id)
FROM
 users
 INNER JOIN events ON events.user_id = users.id
 INNER JOIN accounts ON accounts.id = users.account_id
WHERE
 events.date between <startdate> AND <enddate>
GROUP BY
 users.name,
 accounts.name

Answer 2

The obvious ways of doing it would be:

1) group the results of a join query:

SELECT users.name, accounts.name, COUNT(*)
FROM users, accounts, events
WHERE users.account_id=accounts.id
AND users.id=events.user_id
WHERE events.date>$START_DATE
AND events.date<$END_DATE
GROUP BY users.name, accounts.name;

2) ALternatively you could use the consolidated query on just events as a data source and join to that:

SELECT users.name, accounts.name, ilv.event_count
FROM (SELECT user_id, count(*)
  FROM events
  WHERE events.date>$START_DATE
  AND events.date<$END_DATE
  GROUP BY user_id) as ilv,
users, accounts
WHERE users.id=ilv.user_id
AND users.account_id=accounts.id;

HTH

C.