SELECT SUM() query question

[garlinto]

I have two tables with data schemed as below:

invoice_items
      | ii_id | invoice_id | item_id | item_ref | item_quantity | item_price |
      
      pricelist
 | item_id | ref | part | description | price | cost |

I want to retreive the sum ( invoice_items.item_quantity * pricelist.cost ) for each invoice_item.item_id. I tried this:

SELECT SUM( pl.cost ) AS totalCost
     FROM pricelist pl
 LEFT JOIN invoice_items inv ON inv.item_id = pl.item_id

but I think it gave me a cartesian product since the sum returned was huge.

But when I did this ( which is what actually what I needed ):

SELECT SUM( pl.cost * inv.item_quantity ) AS totalCost
      FROM pricelist pl
      LEFT JOIN invoice_items inv ON inv.item_id = pl.item_id

I think I came up with the right figure.

Retreiving the correct sum is very important. So my question is: Is the second query written correctly, and if not, what should be the correct way to write it?

Thanks

[r937]

if you want a sum for each item_id, then item_id must be in the SELECT and also the GROUP BY

otherwise, you get just one number for the entire table

select pl.item_id
     , sum(pl.cost * inv.item_quantity) as totalcost
  from pricelist pl
left outer
  join invoice_items inv 
    on pl.item_id
     = inv.item_id 
group
    by pl.item_id

[garlinto]

Thank you sir.