Help with query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cone13cone
Forum Newbie
Posts: 24
Joined: Fri Mar 20, 2009 8:32 pm

Help with query

Post 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
Last edited by Benjamin on Mon May 18, 2009 6:34 pm, edited 1 time in total.
Reason: Added [code=sql] tags.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Help with query

Post 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
 
Post Reply