Julian Matz
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER