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:
SELECT
DISTINCT i.jid,
(SELECT SUM(i2.hours) FROM invoices i2 WHERE i.jid = i2.jid) AS hours_total,
(SELECT SUM(i3.materials) FROM invoices i3 WHERE i.jid = i3.jid) AS materials_total,
FROM
invoices i
ORDER BY
i.jid