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?

Dark Falcon · Accepted Answer · 2011-12-23 16:38:29Z

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;

answered Dec 23, 2011 at 16:38

Dark Falcon

44.2k6 gold badges91 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Hoover · Accepted Answer · 2011-12-23 16:39:38Z

0

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?

answered Dec 23, 2011 at 16:39

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

1 Comment

engy0 Over a year ago

tblEvents holds a list of events with point values associated with each. tblPoints uses the employee_id and event_id to pull employee name, event name, and points that have been assigned to an employee within that table.

Collectives™ on Stack Overflow

MySQL Join SUM query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related