aebstract Posted January 7, 2010 Share Posted January 7, 2010 SELECT customfieldview.info, customfieldview.recordid, product.partid, sum(soitem.totalprice) AS totalprice, soitem.productid, product.id FROM customfieldview JOIN product ON customfieldview.recordid = product.partid JOIN soitem ON soitem.productid = soitem.productid WHERE customfieldview.info = 'Fabricated' Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) and if I remove the quotes from fabricated I get: Column unkownFABRICATEDAt line 7, column 30 What I'm trying to do is take every product that has a "customlistitem.name" = "Fabricated", get the product.partid (may result in 1-3 or so rows), use the id column from the product table to get a sum of soitem.totalprice for every product.id that was returned from "Fabricated". I'm needing the value of the total sum for all rows that match these requirements. Any more info needed let me know, thanks. Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/ Share on other sites More sharing options...
aebstract Posted January 7, 2010 Author Share Posted January 7, 2010 Fixed this paragraph: What I'm trying to do is take every product that has a "customfieldview.info" = "Fabricated", get the product.partid that matches the customfieldview.recordid(may result in 1-3 or so rows), use the id column from the product table to get a sum of soitem.totalprice where soitem.productid = product.id, for every product.id that was returned from "Fabricated". I'm needing the value of the total sum for all rows that match these requirements. Any more info needed let me know, thanks. Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/#findComment-990548 Share on other sites More sharing options...
Mchl Posted January 7, 2010 Share Posted January 7, 2010 This query makes no sense. Using aggreagating functions ( sum() ) together with non-aggregated columns gives uncertain/unpredictable results. Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/#findComment-990552 Share on other sites More sharing options...
aebstract Posted January 7, 2010 Author Share Posted January 7, 2010 Okay, that's something I was unaware that I couldn't do, right now I have this: SELECT customfieldview.info, customfieldview.recordid, product.partid, soitem.totalprice AS totalprice, soitem.productid, product.id FROM customfieldview JOIN product ON customfieldview.recordid = product.partid JOIN soitem ON soitem.productid = product.id WHERE customfieldview.info = 'Fabricated' ORDER BY product.id Which is displaying every product.id the correct amount of times, but the value (totalprice) is "null" for every row. Something to do with how it is joined? Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/#findComment-990582 Share on other sites More sharing options...
Mchl Posted January 7, 2010 Share Posted January 7, 2010 And I assume totalprice has correct values in soitem? Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/#findComment-990587 Share on other sites More sharing options...
aebstract Posted January 7, 2010 Author Share Posted January 7, 2010 It does, they are in decimal form. Mostly xxx.xx, some xx.x / x.x and some at 0.0. Some (very few) are going to be negative, but that won't be under "fabricated" it will be in another query I run. Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/#findComment-990588 Share on other sites More sharing options...
Mchl Posted January 7, 2010 Share Posted January 7, 2010 Ok... for start change JOIN to INNER JOIN and say if it changes anything. Link to comment https://forums.phpfreaks.com/topic/187618-help-with-query-sum-of-several-rows/#findComment-990591 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.