SUM function without GROUP

brattt

I have 2 tables: tbl_members and tbl_events. Each member has many events associated with them. Below is my query.

SELECT tbl_members.memberID, tbl_members.firstName, tbl_members.lastName, tbl_events.memberID,date_format(tbl_events.date, '%D %M, %Y') as date, tbl_events.eventName, tbl_events.points, tbl_events.sw_points
FROM tbl_events LEFT JOIN tbl_members ON tbl_events.memberID = tbl_members.memberID
ORDER BY tbl_members.lastName ASC,  tbl_events.date ASC

This returns exactly what I want and looks like this: http://www.cimdev.com.au/test.html

However I need some way to add up the points for each member for each year. I cant put something like: SUM(tbl_events.points) AS totalPoints, becuase then I would have to GROUP BY tbl_events.memberID and would not see all the events for each member. Does anyone have some advice as to how I might tackle this.

bogu

Then GROUP BY events, or create another select that extract for each member the sum of all the events ...

brattt

Thanks for the response bogu. I am having trouble writing another select that extracts for each member the sum of all the events. Could you please give me an idea as to how I might incorporate this into the query.

tomhath

You can get what you need by using a subselect, something like this (untested, you'll probably need to fix something here):

SELECT tbl_members.memberID, tbl_members.firstName, tbl_members.lastName, 
tbl_events.memberID,date_format(tbl_events.date, '%D %M, %Y') as date, 
tbl_events.eventName, tbl_events.points, tbl_events.sw_points,
(select SUM(tbl_events.points) from tbl_events t1 where t1.memberID = t.memberID)  AS totalPoints
FROM tbl_events e
LEFT JOIN tbl_members t ON t.memberID = es.memberID
ORDER BY t.lastName ASC,  e.date ASC

brattt

Thanks tomhath. I tried your code but get an error #1064 which I think is because I am using mysql version 4.0.12 and I think only version 4.1 upwards supports subselects. Any advice?