Struggling with a cross tab query 1

[newmediaguy]

Hi Guys

I have the following SQL statment in SQL Server 2k that I use in an ASP page.

--------------------------------------
SELECT DISTINCT
OrderBody.OrderID AS Expr1, OrderBody.Quantity AS qty, OrderBody.ProductID AS product, OrderBody.CustomerEmail, OrderBody.SellPrice, OrderBody.BuyPrice, OrderBody.Returned, OrderBody.Supplier, OrderBody.SellPrice * OrderBody.Quantity AS Cost, OrderHeaders.DateStamp

FROM user_tbl INNER JOIN OrderBody ON user_tbl.EmailAddress = OrderBody.CustomerEmail INNER JOIN OrderHeaders ON OrderBody.OrderID = OrderHeaders.OrderID
WHERE (user_tbl.EmailAddress'chrisd@hotmail.co.uk')
GROUP BY OrderBody.OrderID, OrderBody.Quantity, OrderBody.ProductID, OrderBody.CustomerEmail, OrderBody.SellPrice, OrderBody.BuyPrice,OrderBody.Returned, OrderBody.Supplier, OrderHeaders.DateStamp
----------------------------------

Sorry its a bit long.

What it is supose to do is grab all of a given users order id's then the products and the total the total amount of products per order and there cost.

for example
-------------------------------
Orderid qty cost
111111 2 20
222222 4 60
-------------------------------

however when i get to an order with more than one product i get this back

-------------------------------
Orderid qty cost
111111 2 20
222222 4 60
222222 2 62
-------------------------------

But i just want distinct (orderid) order total for all products

Any thoughts would be appreciated, sorry it's so big, i can email a stripped down version if you would like.

Many thanks

Glen
glen@b-n-m.com

 

[mrees]

So something like

SELECT
  OrderBody.OrderID,
  sum(OrderBody.quantity) qty,
  sum(OrderBody.SellPrice*OrderBody.quantity)cost
FROM 
  user_tbl 
JOIN 
  OrderBody ON user_tbl.EmailAddress = OrderBody.CustomerEmail 
JOIN
  OrderHeaders ON OrderBody.OrderID = OrderHeaders.OrderID  
WHERE     
 user_tbl.EmailAddress'chrisd@hotmail.co.uk'
GROUP BY
 OrderBody.OrderID

Does this do the job?

mrees

 

[newmediaguy]

Thanks Mrees

that works perfectly, just as a side note could you insert some commets in to it so that i may keep it in my code archive as it works perfectly

Really appreciate your help and quick response.

Many thanks

Glen

 

[mrees]

Ok - Here goes. I'll add a couple of other tips which may make your life easier

[COLOR=green]/*
Code to return the number of items and total value of
each order
*/[/color]
[COLOR=blue]SELECT[/color]
  ob.OrderID,
  [COLOR=magenta]sum[/color](ob.quantity) qty,
  [COLOR=magenta]sum[/color](ob.SellPrice*ob.quantity)cost
[COLOR=blue]FROM[/color]
[COLOR=green]/*
Aliases (in [COLOR=red]red [/color]) have been assigned to each table to make the query more readable
*/[/color]
  user_tbl [COLOR=red]u[/color]
[COLOR=GRAY]JOIN[/COLOR] [COLOR=green]-- INNER is not needed as it is the default join type[/color]
  OrderBody [COLOR=red]ob[/color] ON u.EmailAddress = ob.CustomerEmail 
[COLOR=GRAY]JOIN[/COLOR]
  OrderHeaders [COLOR=red]oh[/color] ON ob.OrderID = oh.OrderID  
[COLOR=blue]WHERE[/color]     
 u.EmailAddress = [COLOR=red]'chrisd@hotmail.co.uk'[/color]
[COLOR=blue]GROUP BY[/color]
 ob.OrderID
[COLOR=green]/* 
As ob.quantity and ob.sellprice are in aggregate functions, they do not need to appear in the GROUP BY.
Basically you are saying, for each distinct OrderID, sum the number held in each records quantity and sum the result of quantity*price
*/
[/color]

Cheers
Martin

 

[newmediaguy]

Thank you Martin

Not only for sorting my prob but for the extra work of breaking it down for me, now I understand.

Once again thanks

Glen