Link to home
Start Free TrialLog in
Avatar of asrgrant

asked on

Sum multiple fileds in a join but return one record that they belong to

Hi .. Im trying (as im a beginner) to join a lot of tables and sum one of the fields in one of the tables. See attached.

Im trying to display a record set that sums the amount of hours (tbl unit_time.hours) that a person (tbl personnel.personID) worked on a single task (tbl t_persontask.taskID). Ive had a go but the result set shows the hours worked , not summed, but also repeats the taskID in a repeat region. Im stuck.

I am using this ...

SELECT t_persontask.PersonID, personnel.*, tasking.*, customer.*, system_set.*
FROM personnel INNER JOIN (((tasking INNER JOIN t_persontask ON tasking.taskID = t_persontask.taskID) INNER JOIN system_set ON tasking.sys_setID = system_set.sys_setID) INNER JOIN customer ON tasking.customerID = customer.customerID) ON personnel.PersonID = t_persontask.personID
WHERE personnel.PersonID=MMColParam

... and I want to try and sum up the hours worked for an individual. The screen shot attached shows the output..(see screenshot.gif)
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

use aggregate query

see this demo
Using wild cards (*) in this type of query can mess things up.

The best approach is to select the bare minimum specific fields that you need in order to display the needed results (generally all fields from all tables are not needed).

Once you do that, click the summation sign in the Query Builder and choose the field that you want to sum, and Group By the others.  

The reason for using a bare minimum of fields is that if other fields for a given person differ, you might not get the Sums per person -- they may appear grouped by other fields as well.

The idea is to isolate the field that you want to Sum, and make sure that any other selected fields for a given person have the values in the records.
Avatar of asrgrant


Many thanks for that. I do use SUM and Grouping, guess im not doing well enough. I think im looking to do a sub-query suming the hours and then showing the rest of the detail
So here is where Im at ...

Main Query .......

FROM ((customer INNER JOIN tasking ON customer.customerID = tasking.customerID) LEFT JOIN system ON tasking.sysID = system.SysID) LEFT JOIN system_set ON tasking.sys_setID = system_set.sys_setID
WHERE tasking.tasklocation=1 AND NOT tasking.ttasktype='4' AND NOT tasking.tstatus='Closed'

I think I want to nest the  query below  in the one above, so to see the amount of hours (unit_time.hours) spent on this task (tasking.taskID)

SELECT tasking.taskID, Sum(unit_time.hours) AS SumOfhours
FROM tasking INNER JOIN unit_time ON tasking.taskID = unit_time.taskID
GROUP BY tasking.taskID

Hope this makes sense. Just having trouble SUMMING the unit_hours and outputting this as part of the main query
Avatar of mbizup
Flag of Kazakhstan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial