Php – Add all lines multiplied by another line in another table

MySQLPHPpostgresql

I hope I can explain this good enough. I have 3 tables. wo_parts, workorders and part2vendor. I am trying to get the cost price of all parts sold in a month. I have this script.

$scoreCostQuery = "SELECT SUM(part2vendor.cost*wo_parts.qty) as total_score 
                       FROM part2vendor 
                       INNER JOIN wo_parts 
                         ON (wo_parts.pn=part2vendor.pn)  
                       WHERE workorder=$workorder";

What I am trying to do is each part is in wo_parts (under partnumber [pn]). The cost of that item is in part2vendor (under part number[pn]). I need each part price in part2vendor to be multiplied by the quantity sold in wo_parts. The way all 3 tie up is workorders.ident=wo_parts.workorder and part2vendor.pn=wo_parts.pn. I hope someone can assist. The above script does not give me the same total as when added by calculator.

Best Answer

This is not an answer, just a comment.

Why don't you take the sum/multiply operation outside the SQL statement? I know, that seems stupid because it will increase the lines of code and the complexity of the script, but, imho, it is always a good thing to keep code and SQL statements as far away as possible.

Related Solutions

Mysql – How to see all foreign keys to a table or column

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

Php – Display rows based on $array

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;

Best Answer

Related Solutions

Mysql – How to see all foreign keys to a table or column

Php – Display rows based on $array

Related Topic