Help with query - sum of several rows

[aebstract]

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.

[aebstract]

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.

[Mchl]

This query makes no sense. Using aggreagating functions ( sum() ) together with non-aggregated columns gives uncertain/unpredictable results.

[aebstract]

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?

[Mchl]

And I assume totalprice has correct values in soitem?

[aebstract]

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.

[Mchl]

Ok... for start change JOIN to INNER JOIN and say if it changes anything.