Joining two tables with a sum( ) table
SELECT firstName + ' ' + lastName as Name,
       RTS_ROLE.roleDescription as `Current Role`,
FROM RTS_STAFF, RTS_ROLE
WHERE RTS_STAFF.staffRole = RTS_ROLE.roleID
AND staffDeleted <> true
ORDER BY roleID;

SELECT RTS_STAFF.staffId, Sum(IIf(Isnull(Monday),0,Monday)
+IIf(Isnull(Tuesday),0,Tuesday)
+IIf(Isnull(Wednesday),0,Wednesday)
+IIf(Isnull(Thursday),0,Thursday)
+IIf(Isnull(Friday),0,Friday)) AS `Weekly Hours`
FROM WORKING_HOURS, RTS_STAFF
WHERE RTS_STAFF.staffId = WORKING_HOURS.staffID
AND Date() > WORKING_HOURS.EffectiveFrom
GROUP BY RTS_STAFF.staffId;

Table structure:
RTS_ROLE: (lookup table)
(pk)RoleID: number
roleDescription: string

RTS_STAFF
(pk)staffId: number
firstName: string
lastName: string
staffRole: number (fk to RTS_ROLE.roleID)
staffDeleted: boolean

WORKING_HOURS:
(pk)workingWeekID: number
staffID: number (fk to RTS_STAFF.staffId)
Monday: number
Tuesday: number
...
Friday:number
EffectiveFrom: date

SELECT staff.staffid,
       staff.firstname + ' ' + staff.lastname AS Name,
       role.roledescription                   AS `Current Role`,
       Sum(IF(Isnull(monday), 0, monday)
           + IF(Isnull(tuesday), 0, tuesday)
           + IF(Isnull(wednesday), 0, wednesday)
           + IF(Isnull(thursday), 0, thursday)
           + IF(Isnull(friday), 0, friday))   AS `Weekly Hours`
FROM   working_hours,
       rts_staff staff,
       rts_role role
WHERE  staff.staffrole = role.roleid
       AND staff.staffdeleted <> true
       AND staff.staffid = working_hours.staffid
       AND Now() > working_hours.effectivefrom
GROUP  BY staff.staffid,
          staff.firstname + ' ' + staff.lastname,
          role.roledescription

CREATE TABLE `rts_staff` (
  `staffid` int(11) NOT NULL,
  `staffrole` int(11) DEFAULT NULL,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `staffdeleted` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`staffid`)
) ENGINE=InnoDB


CREATE TABLE `rts_role` (
  `roleid` int(11) NOT NULL,
  `roledescription` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`roleid`)
) ENGINE=InnoDB


CREATE TABLE `working_hours` (
  `staffid` int(11) NOT NULL,
  `effectivefrom` date DEFAULT NULL,
  `monday` int(11) DEFAULT NULL,
  `tuesday` int(11) DEFAULT NULL,
  `wednesday` int(11) DEFAULT NULL,
  `thursday` int(11) DEFAULT NULL,
  `friday` int(11) DEFAULT NULL,
  PRIMARY KEY (`staffid`)
) ENGINE=InnoDB

SELECT firstName + ' ' + lastName as Name,
   RTS_ROLE.roleDescription as `Current Role`, x.weeklyhours
   FROM RTS_STAFF s INNER JOIN
   RTS_ROLE r ON s.staffRole = r.roleID INNER JOIN
   (
     SELECT RTS_STAFF.staffId, Sum(IIf(Isnull(Monday),0,Monday)
     +IIf(Isnull(Tuesday),0,Tuesday)
     +IIf(Isnull(Wednesday),0,Wednesday)
     +IIf(Isnull(Thursday),0,Thursday)
     +IIf(Isnull(Friday),0,Friday)) AS weeklyhours
     FROM WORKING_HOURS, RTS_STAFF
     WHERE RTS_STAFF.staffId = WORKING_HOURS.staffID
     AND Date() > WORKING_HOURS.EffectiveFrom
     GROUP BY RTS_STAFF.staffId
   ) x ON x.staffID = s.staffID
WHERE  s.staffDeleted <> true
ORDER BY r.roleID;