Count of rows returned by PO Number

[Cpreston]

Hi

Sorry to bother you peeps again but would like some help on a COUNT. I want to return the count of rows returned by PO number and add it into a column at the end of the query. So if I return 8 rows I expect the number at the end to be 8. The next PO may have 5 so 5 would be displayed. I have been playing in the area where it says SUM (Distinct) but using count. Just cant seem tog et it working, any ideas please. Many Thanks

SELECT     dbo.PurchaseOrderHeader.DateTimeCreated, dbo.PurchaseOrderHeader.DateExpected, dbo.PurchaseOrderLine.Quantity, dbo.PurchaseOrderLine.QuantityReceived, 
                      dbo.PurchaseOrderLine.Quantity - dbo.PurchaseOrderLine.QuantityReceived AS PoOutstandingQty, 
                      dbo.PurchaseOrderLine.UnitCostPrice * dbo.PurchaseOrderLine.Quantity + dbo.PurchaseOrderLine.TotalAdditionalCost AS PoOriginalValue, 
                      dbo.PurchaseOrderLine.TotalAdditionalCost / NULLIF (dbo.PurchaseOrderLine.TotalVolume, 0) AS AdditonalCost, dbo.PurchaseOrderLine.UnitCostPrice AS CostperM3, 
                      dbo.PurchaseOrderLine.TotalAdditionalCost / NULLIF (dbo.PurchaseOrderLine.TotalVolume, 0) + dbo.PurchaseOrderLine.UnitCostPrice AS CombineCostM3, 
                      dbo.PurchaseOrderHeader.TimberMill, dbo.Supplier.Name, dbo.PurchaseOrderHeader.OrderStatus AS DetermineOUtstandingPO, 
                      dbo.PurchaseOrderLine.LineStatus AS IndetfyOrderComplete, dbo.Product.ProductCode, dbo.Product.Description, dbo.Branch.Name AS Quay, 
                      dbo.Users.Name AS POCreated, dbo.PurchaseOrderHeader.OrderType, dbo.PurchaseOrderHeader.PurchaseOrderNumber, 
                      dbo.StockReceiptLine.QuantityReceived AS SRQtyReceived, dbo.StockReceiptHeader.DateReceived
                      ---SUM(DISTINCT (dbo.StockReceiptHeader.StockReceiptNumber))AS Stock
                      FROM    dbo.PurchaseOrderHeader INNER JOIN
                      dbo.PurchaseOrderLine ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.PurchaseOrderLine.PurchaseOrderID INNER JOIN
                      dbo.Supplier ON dbo.PurchaseOrderHeader.SupplierID = dbo.Supplier.SupplierID INNER JOIN
                      dbo.Product ON dbo.PurchaseOrderLine.ProductID = dbo.Product.ProductID INNER JOIN
                      dbo.Branch ON dbo.PurchaseOrderHeader.BranchID = dbo.Branch.BranchID INNER JOIN
                      dbo.Users ON dbo.PurchaseOrderHeader.CreatedByID = dbo.Users.UserID INNER JOIN
                      dbo.StockReceiptLine ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.StockReceiptLine.PurchaseOrderID AND 
                      dbo.PurchaseOrderLine.PurchaseOrderLineID = dbo.StockReceiptLine.PurchaseOrderLineID AND dbo.Product.ProductID = dbo.StockReceiptLine.ProductID INNER JOIN
                      dbo.StockReceiptHeader ON dbo.StockReceiptLine.StockReceiptID = dbo.StockReceiptHeader.StockReceiptID
WHERE     (dbo.PurchaseOrderHeader.Deleted = 0)
GROUP BY dbo.PurchaseOrderHeader.DateTimeCreated, dbo.PurchaseOrderHeader.DateExpected, dbo.PurchaseOrderLine.Quantity, dbo.PurchaseOrderLine.QuantityReceived, 
                      dbo.PurchaseOrderLine.Quantity - dbo.PurchaseOrderLine.QuantityReceived, 
                      dbo.PurchaseOrderLine.UnitCostPrice * dbo.PurchaseOrderLine.Quantity + dbo.PurchaseOrderLine.TotalAdditionalCost, 
                      dbo.PurchaseOrderLine.TotalAdditionalCost / NULLIF (dbo.PurchaseOrderLine.TotalVolume, 0), dbo.PurchaseOrderLine.UnitCostPrice, 
                      dbo.PurchaseOrderLine.TotalAdditionalCost / NULLIF (dbo.PurchaseOrderLine.TotalVolume, 0) + dbo.PurchaseOrderLine.UnitCostPrice, 
                      dbo.PurchaseOrderHeader.TimberMill, dbo.Supplier.Name, dbo.PurchaseOrderHeader.OrderStatus, dbo.PurchaseOrderLine.LineStatus, dbo.Product.ProductCode, 
                      dbo.Product.Description, dbo.Branch.Name, dbo.Users.Name, dbo.PurchaseOrderHeader.OrderType, dbo.PurchaseOrderHeader.PurchaseOrderNumber, 
                      dbo.StockReceiptLine.QuantityReceived, dbo.StockReceiptHeader.DateReceived
HAVING      (dbo.PurchaseOrderHeader.OrderType = 1) AND (dbo.PurchaseOrderHeader.PurchaseOrderNumber = 35489)

 

[Andrzejek]

Wouldn't that be simply:

...
dbo.StockReceiptLine.QuantityReceived AS SRQtyReceived, dbo.StockReceiptHeader.DateReceived,[blue]
(SELECT COUNT(PurchaseOrderNumber) FROM dbo.PurchaseOrderHeader POH
WHERE PurchaseOrderNumber = dbo.PurchaseOrderHeader.PurchaseOrderNumber) AS Stock[/blue]
FROM    dbo.PurchaseOrderHeader INNER JOIN
...

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Thanks for the reply. It is still retuning 1 in the Stock field and I ma trying to get 8 for example as there are 8 rows.

Thanks

 

[Andrzejek]

Investigate the code in [blue]BLUE[/blue] from my post.
I may have the table/field wrong, but you should get the gist of it.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Thinking about it I tried it on date but still only 1 coming up.
I will keep having a go, thanks for the script.

 

[Andrzejek]

If PurchaseOrderNumber [blue]1234[/blue] has 8 records, the SELECT should return 8, because what it does is simply:
[pre]

SELECT COUNT(PurchaseOrderNumber) FROM dbo.PurchaseOrderHeader
WHERE PurchaseOrderNumber = [blue]1234 [/blue]
[/pre]

---- Andy

There is a great need for a sarcasm font.

 

[gbaughma]

If PurchaseOrderNumber 1234 has 8 records, the SELECT should return 8, because what it does is simply:

SELECT COUNT(PurchaseOrderNumber) FROM dbo.PurchaseOrderHeader
WHERE PurchaseOrderNumber = 1234

Except, in a normal PO system, it wouldn't. The PO number would/should be a key field.
Now, if you took the PO header, and joined it to the Purchase Order Lines, then did something like:

SELECT COUNT(PurchaseOrderNumber) FROM PurchaseOrderHeader LEFT JOIN PurchaseOrderLines ON PurchaseOrderHeader.PurchaseOrderNumber = PurchaseOrderLines.PurchaseOrderNumber WHERE
PurchaseOrderLines.ItemNumber = 1234

... that would give you a count of all purchase orders that included that part number.

Purchase Order Header should be a one-to-many with PurchaseOrderLines. There should not be duplicate purchase order headers, the purchaseorderlines should join with the purchase order on the purchase order number.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[gbaughma]

... or, if you wanted to find out how many items were on a specific purchase order, you may be able to just work off of purchaseorderlines, since the PO number should be your joining field...

So, as an example, if you had (super simplifed)

PurchaseOrderHeader
PO Number Name
1234 John Doe
1250 Ralph Hapshat

PurchaseOrderLines
PO Number Item Qty
1234 Widget 1 2
1234 Widget 2 5
1250 Whatsit 1 1
1250 Whatsit 2 2

... To find out how many of Widget 1 were ordered, you could just:
SELECT Qty FROM PurchaseOrderLines WHERE [PO Number] = 1234

.... you shouldn't have to join PurchaseOrderLines and PurchaseOrderHeader togeter, since the PO Number is the joining field.

I hope I'm making sense.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg