SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: SELECT SUM() query question
-
Jan 27, 2005, 18:29 #1
- Join Date
- Jun 2002
- Location
- Riding the electron wave
- Posts
- 372
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 |
Code:SELECT SUM( pl.cost ) AS totalCost FROM pricelist pl LEFT JOIN invoice_items inv ON inv.item_id = pl.item_id
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
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?
ThanksDucharme's Axiom: "If you view your problem closely
enough, you will recognize yourself as part of the problem."
-
Jan 27, 2005, 19:50 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 28, 2005, 12:36 #3
- Join Date
- Jun 2002
- Location
- Riding the electron wave
- Posts
- 372
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you sir.
Ducharme's Axiom: "If you view your problem closely
enough, you will recognize yourself as part of the problem."
Bookmarks