nerhael
04-01-2004, 04:41 PM
Is it possible to do something to this effect. I have two tables, items, and items_received.
Items stores a cost, itemId, and orderId. items_received has orderId, itemId, quantityReceived.
I want to write one query to do the following. Give me the total value of all items received to date for that orderId. The received data can have data like so:
Order Id Item Id Qty
1 2 1
1 2 2
1 3 2
So you can have multiple receive lines. So you'd have to sum the quantities received, then * by item cost, then sum that value for each item.
I had initially tried something like this:
select items, SUM(SUM(por.quantityReceived * IF(poi.costPerUnit is not null, poi.costPerUnit, bs.cost))) as cost
FROM purchaseorderitems poi, purchaseorderreceipts por, purchaseorders po
LEFT JOIN booksuppliers bs ON po.supplierId = bs.supplierId
AND poi.bookId = bs.bookId
WHERE poi.poId = po.poId
AND poi.poId = por.poId
AND poi.lineNumber = por.lineNumber
GROUP by poi.poId
But claims invalid use of group function. I could naturally just do the group based on itemId, and get the quantity received cost, and loop through code to get final cost...but hey, why split the task to two places? If I can do this in pure SQL, would make me happy. Let me know.
Pete.
PS, there a way to submit something a nice table form? Had originally done it in HTML, but that didn't work over so well.
Items stores a cost, itemId, and orderId. items_received has orderId, itemId, quantityReceived.
I want to write one query to do the following. Give me the total value of all items received to date for that orderId. The received data can have data like so:
Order Id Item Id Qty
1 2 1
1 2 2
1 3 2
So you can have multiple receive lines. So you'd have to sum the quantities received, then * by item cost, then sum that value for each item.
I had initially tried something like this:
select items, SUM(SUM(por.quantityReceived * IF(poi.costPerUnit is not null, poi.costPerUnit, bs.cost))) as cost
FROM purchaseorderitems poi, purchaseorderreceipts por, purchaseorders po
LEFT JOIN booksuppliers bs ON po.supplierId = bs.supplierId
AND poi.bookId = bs.bookId
WHERE poi.poId = po.poId
AND poi.poId = por.poId
AND poi.lineNumber = por.lineNumber
GROUP by poi.poId
But claims invalid use of group function. I could naturally just do the group based on itemId, and get the quantity received cost, and loop through code to get final cost...but hey, why split the task to two places? If I can do this in pure SQL, would make me happy. Let me know.
Pete.
PS, there a way to submit something a nice table form? Had originally done it in HTML, but that didn't work over so well.