Page 1 of 1

Help with query

Posted: Mon May 18, 2009 6:28 pm
by cone13cone
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:

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
 
Please help

Re: Help with query

Posted: Mon May 18, 2009 6:40 pm
by Benjamin
I think this is about the best you can do. You'll need a separate query to pull the cids for a specific jid.

Code: Select all

 
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