SELECT SUM() query question

I have two tables with data schemed as below:

Code:

---

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:

Code:

---

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 ):

Code:

---

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 :)

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

Code:

---

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

---

Thank you sir. :)