MySQL Join SUM query

Question

Thank you for looking at my issue.
Right now I have 3 tables:

• tblEmployee
• tblEvents
• tblPoints

tblEmployee

Employee_id
First_Name
Last_Name
Email
Team

tblEvents

Events_id
Event_Date
Event_Name
Points
Active

tblPoints

Points_id
Employee_id
Events_id

Here is the query I am currently running:

SELECT tblEmployee.First_Name, tblEmployee.Last_Name, tblEvents.Points
FROM tblEvents INNER JOIN (tblEmployee INNER JOIN tblPoints
ON tblEmployee.Employee_id = tblPoints.Employee_id) 
ON tblEvents.Events_id = tblPoints.Events_id;

This query returns all rows within the Points table but I need it to only return one row, per employee, with the points column being SUM. I have tried this but end up with one row that sums all points. Any thoughts?

Answer 1

Does this work?

select tblEmployee.first_name, 
       tblEmployee.Last_Name
       sum(tblEvents.Points)
from tblEmployee 
  join tblPoints on tblPoints.employee_id = tblEmployee.employee_id
group by tblEmployee.first_name, 
       tblEmployee.Last_Name

MySQL will gladly group without the group by, where other DBs would throw an error.

The question I have is why are you joining tblEvents? What information do you want from that table?

Answer 2

Have you tried something like:

SELECT tblEmployee.First_Name, tblEmployee.Last_Name, SUM(tblEvents.Points) AS Points
FROM tblEvents INNER JOIN (tblEmployee INNER JOIN tblPoints
ON tblEmployee.Employee_id = tblPoints.Employee_id) 
ON tblEvents.Events_id = tblPoints.Events_id
GROUP BY tblEmployee.Employee_id;