Display rows based on $array

Question

I have a table in postgres called workorders. In it are various headings. The ones I am interested in are labor, date_out and ident. This table ties up with wo_parts (workorder parts). In this table are the headings I am interested in, part and workorder. Both are integers. (part auto number) The final table is part2vendor and the headings are retail and cost. Right, basically what happens is.....I create a workorder (invoice). This calls a part from part2vendor. I enter it and invoice it off. In workorder a row is created and saved. It is given an ident. In wo_parts, the part i used is recorded as well as workorder number and qty used. What I want to do is create a report in php that pools all this info on one page. IE. if i choose dates 2009-10-01 to 2009-10-31 it will pull all workorders in this range and tell me the total labour sold and then the PROFIT (retail less cost) of the parts I sold, using these 3 tables. I hope i have explained as clear as possible. any questions please ask me. Thank you very much for your time.

Answer 1

You will want to read up on SQL - keywords to look for include "aggregate", "SUM" and "GROUP BY".

You query will look something like (but this will certainly need correcting):

SELECT SUM(wo.labor) AS tot_labor, SUM(p2v.cost - p2v.retail) AS tot_profit FROM workorders AS wo JOIN wo_parts AS wp ON wo.ident=wp.ident [?] JOIN part2vendor AS p2v ON ...something... WHERE date_out BETWEEN '2009-10-01'::date AND '2009-10-31'::date;