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


LVL 21
Julian MatzJoint ChairpersonAsked:
Who is Participating?
 
rjdownConnect With a Mentor Commented:
I saw your other recent question on queries... I'm glad you decided to normalize your database as it makes this sort of thing so much easier!
SELECT e.id,
(SELECT count( id )	FROM referrals WHERE sent = e.id) AS sent,
(SELECT count( id ) FROM referrals WHERE received = e.id) AS received
FROM employees e

Open in new window

0
 
rjdownConnect With a Mentor Commented:
Oh sorry, you wanted the name too!


SELECT e.id, e.name,
(SELECT count( id ) FROM referrals WHERE sent = e.id) AS sent,
(SELECT count( id ) FROM referrals WHERE received = e.id) AS received
FROM employees e

Open in new window

0
 
Julian MatzJoint ChairpersonAuthor Commented:
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.