Link to home
Start Free TrialLog in
Avatar of Julian Matz
Julian MatzFlag for Ireland

asked on

MySQL COUNT and GROUP BY

Hi! I've a small problem I could use some help with...

I have a mysql table that looks like this:

id     ymd           sent (id)    received (id)
------------------------------------------------
1     2010-04-15     5               6
2     2010-04-16     5               7
3     2010-04-16     7               4
4     2010-04-17     4               6

This table logs referrals that are sent and received between employees. I also have another table "employees" that holds the employees' names, etc. The "sent" and "received" fields above store the employee's ID, which corresponds with the "id" field of the "employees" table.

In my example above, employee 5 sent a referral to employee 6 on the 15th April, and so on.

I need to generate a report that shows how many referrals were sent and received by each employee. This report has to look like this:

Employee ID, Name     Referrals Sent   Referrals Received
------------------------------------------------------------------------
5 John Doe                     2                         0
7 Jane Doe                     1                         1
4 Joe Bloggs                   1                         1
6 Roger Rabbit               0                         2


My sample table corresponds with my sample report.

I tried using the below query, which didn't work out too well, and I was hoping someone could lend a hand.

SELECT t1.id, CONCAT(t1.first_name,' ',t1.last_name) AS `name`,
                  COUNT(t2.sent) AS `count1`,
                  COUNT(t3.received) AS `count2`
                  FROM employees AS t1
                  LEFT OUTER JOIN referrals AS t2
                  ON t1.id = t2.sent
                  LEFT OUTER JOIN referrals AS t3
                  ON t1.id=t3.received
                  GROUP BY name


ASKER CERTIFIED SOLUTION
Avatar of rjdown
rjdown
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Julian Matz

ASKER

Excellent! Thanks! I've been wrecking my head over this for a while now. But it seems from your solution that I might have been over-complicating things :)