Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
swissivan
Starting Member
30 Posts |
Posted - 2013-03-04 : 20:57:58
|
DECLARE @product_ids TABLE (id UNIQUEIDENTIFIER)-- get all product ids in a PO , e.g. '1111' for ProductA, '2222' for ProductBINSERT INTO @product_ids(id)SELECT product_id FROM PurchaseOrderWHERE number = 'PO1303050001'I want to get a list of product with total quantitye.g.1111 | 22222 | 5but the following code only get total of all productsSELECT id, SUM(quantity) FROM PurchaseOrderProduct WHERE id IN (SELECT * FROM @product_ids))any comments, thanks. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-04 : 22:50:33
|
[code]SELECT po.product_id,pop.Qty FROM PurchaseOrder poINNER JOIN (SELECT id, SUM(quantity) AS Qty FROM PurchaseOrderProduct GROUP BY id) popON pop.id = po.product_id WHERE po.number = 'PO1303050001'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
swissivan
Starting Member
30 Posts |
Posted - 2013-03-05 : 20:06:16
|
quote: Originally posted by visakh16
SELECT po.product_id,pop.Qty FROM PurchaseOrder poINNER JOIN (SELECT id, SUM(quantity) AS Qty FROM PurchaseOrderProduct GROUP BY id) popON pop.id = po.product_id WHERE po.number = 'PO1303050001' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
GROUP BY works great, thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 01:02:18
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|