Help with query
Posted: Mon May 18, 2009 6:28 pm
Hello guys I'm having a very rough time with this one:
I have a table called invoices with fields JID, CID, hours, materials
I need to count for multiple JID(s) and then SUM(hours) and SUM(materials) for that JID then CONCAT cids
The CID is the contractor ID and sometimes multiple contactors do the same job(JID) so i need to total their materials and hours. So far this is what I have come up with:
Please help
I have a table called invoices with fields JID, CID, hours, materials
I need to count for multiple JID(s) and then SUM(hours) and SUM(materials) for that JID then CONCAT cids
The CID is the contractor ID and sometimes multiple contactors do the same job(JID) so i need to total their materials and hours. So far this is what I have come up with:
Code: Select all
SELECT JID, SUM( hours ) , SUM( materials )
FROM (
SELECT JID AS job_number, COUNT( * ) AS dup
FROM invoices
GROUP BY JID
) AS tablex
WHERE dup > 1