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 M.Web DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rjdownCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rjdownCommented:
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 M.Web DeveloperAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.