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 |
|
superhero
Yak Posting Veteran
52 Posts |
 Posted - 2008-02-05 : 07:35:27
|
| HiI have a table with [ORDERHEADER] and [ORDERDETAILS].[ORDERHEADER].ORDERID = ORDERDETAILS.ORDERIDOrder Details would typically look like this:ORDERID | PRODUCT CODE | QUANTITY | PRICE1 FI44321 2 200.001 FI33221 3 210.002 ... ETC...I want to run a query that'll tell me how many products where ordered grouped by each order.EG:ORDERID 1 ordered 5 products at a total of 410.00ORDERID 2 ORDERED 1 PRODUCT AT A TOTAL OF 100.00I'm using this query - it works but only if the productcodes are all the same, otherwise I get ORDERID = 1 TWICE IN THE RESULTS FOR INSTANCE.SELECT ORDERHEADER.OrderID AS ORDERID, ORDERHEADER.OrderNumber AS 'YOUR ORDER NO', ORDERHEADER.CLGOrderNo AS 'OUR REF.', ORDERDETAILS.Quantity * COUNT(*) AS ORDERTOTAL, 0 AS DELIVERED, 0 AS BACKORDERED, '-' AS ETA, ORDERHEADER.OrderDate AS 'ORDER DATE', { fn UCASE(USERS.Fullname) } AS 'USER'FROM ORDERHEADER INNER JOIN ORDERDETAILS ON ORDERHEADER.OrderID = ORDERDETAILS.OrderID INNER JOIN USERS ON ORDERHEADER.UserID = USERS.UserIDWHERE (ORDERHEADER.AccountingCode = 'datcon')GROUP BY ORDERHEADER.OrderID, ORDERHEADER.OrderNumber, ORDERHEADER.CLGOrderNo, ORDERHEADER.OrderDate, USERS.Fullname, ORDERDETAILS.Quantity |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2008-02-05 : 08:26:41
|
| [code]SELECT *FROM OrderHeader OH JOIN ( SELECT OD.OrderID ,COUNT(DISTINCT ProductCode) AS ProductCount ,SUM(OD.Quantity * OD.Price) AS OrderCost FROM OrderDetails OD GROUP BY OD.OrderID ) D ON OH.OrderID = D.OrderID JOIN Users U ON OH.UserID = U.UserIDWHERE OH.AccountingCode = 'datcon'[/code] |
 |
|
|
|
|
|
|
|
COUNT(*) products ordered