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

asrgrant
asrgrant used Ask the Experts™
on
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
ORDER BY taskref DESC

... and I want to try and sum up the hours worked for an individual. The screen shot attached shows the output..(see screenshot.gif)
taskingj-oins.gif
taskingj-oins2.gif
screenshot.gif
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
use aggregate query

see this demo
http://www.youtube.com/watch?v=AykV47ObmFA
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

Commented:
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

Author

Commented:
So here is where Im at ...

Main Query .......

SELECT *
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'
ORDER BY taskref DESC


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
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
This is really difficult to follow without actually having a copy of your database to dig into, but you can try this:



SELECT *
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 LEFT JOIN
(
SELECT tasking.taskID AS tID, Sum(unit_time.hours) AS SumOfhours
FROM tasking INNER JOIN unit_time ON tasking.taskID = unit_time.taskID
GROUP BY tasking.taskID
) AS qSub on qSub.tID= tasking.TaskID

WHERE tasking.tasklocation=1 AND NOT tasking.ttasktype='4' AND NOT tasking.tstatus='Closed'
ORDER BY taskref DESC

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial