Need a query to add items to list

[elentz]

I probably didn't state what I need very well in the subject, but here goes.  I have a CRM system that allows me to create quotes to my customers.  Sometimes we will have to put together a quote for the same customer but different quotes for separate locations.  Within each quote there would be common items.  What I would like to do is have a query that would do a sum of all the items, for instance if two quotes had 3 widgets in one and 5 widgets in the other then the query result would show 8 widgets.  Of course this is an over simplification, there could and would be many common items between the quotes.  Here is a query that I use to get the single quote items.

SELECT
vtiger_inventoryproductrel.quantity,
vtiger_products.productcode,
vtiger_products.mfr_part_no,
vtiger_products.productname,
vtiger_productcf.cf_450,
vtiger_inventoryproductrel.listprice,
quantity * cf_450 AS ext_cost,
listprice * quantity AS ext_quote
FROM
vtiger_inventoryproductrel
Inner Join vtiger_products ON vtiger_products.productid = vtiger_inventoryproductrel.productid
Inner Join vtiger_productcf ON vtiger_productcf.productid = vtiger_products.productid
WHERE
vtiger_inventoryproductrel.comment NOT LIKE  'Part of Kit' AND
vtiger_inventoryproductrel.id =  '$myvar'

 

The variable '$myvar' I am using from a page to get the quote number, I am using it on a page that uses baaGrid to list all the parts for that particular quote.  Any help is greatly appreciated!

[elentz]

I've gotten a little farther in my quest.  Using this query I can get all the items in a single quote grouped but it will not add the quantities of similar productcode items.  The reason for this is that I get parts that come with a kit and then I need to add some of the same parts to finish out the quote.  The whole purpose of this is to get an overall count on all the things that need to be ordered.

SELECT
Sum(vtiger_inventoryproductrel.quantity),
vtiger_products.productcode,
vtiger_products.productname,
vtiger_inventoryproductrel.listprice,
vtiger_inventoryproductrel.id
FROM
vtiger_inventoryproductrel
Inner Join vtiger_products ON vtiger_products.productid = vtiger_inventoryproductrel.productid
Inner Join vtiger_productcf ON vtiger_productcf.productid = vtiger_products.productid
WHERE
vtiger_inventoryproductrel.id =  '18455'
GROUP BY
vtiger_products.productcode,
vtiger_products.productname,
vtiger_inventoryproductrel.listprice,
vtiger_inventoryproductrel.id

 

I also need to be able to add more of the vtiger_inventoryproductrelid.  If I use one id then I get a result, if I use something like vtieger_inventoryproductrelid = 1234 and 5678 then I get no results.

 

Thanks for any insight you can share

[fenway]

I don't understand.