SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 calculate qty and total price		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 08/21/2006 :  04:15:08  Show Profile  Reply with Quote
Hi

This is a part of q query I have the need to display the total sum of the "Qty" and dbo.tbl_Order.ProductPrice

SELECT DISTINCT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty

How do I get this to work?

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 08/21/2006 :  04:23:29  Show Profile  Reply with Quote
Don't quite understand what you want here. Maybe you can post the table DDL, some sample data and the result that you want.

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/21/2006 :  04:33:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try this
SELECT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty, MAX(dbo.tbl_Order.ProductPrice) GROUP BY dbo.tbl_Order.NodeID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 08/21/2006 :  04:44:12  Show Profile  Reply with Quote
Hi

There are 4 different tables and the complete query looks like this...

SELECT DISTINCT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty, dbo.tbl_Products.Text, dbo.tbl_Login.CustID
FROM dbo.tbl_Order INNER JOIN
dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
WHERE (dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-05-01', 102))
GROUP BY dbo.tbl_Order.NodeID, dbo.tbl_Products.Text, dbo.tbl_Login.CustID
HAVING (dbo.tbl_Login.CustID = 20)


That query give me a result like this...


NodeID Qty Text CustID
1 200 prod 1 5
2 150 prod 2 5
3 400 prod 3 5


But I would also like to display the total sum like this...


NodeID Qty Text CustID Price TotalSum
1 200 prod 1 5 10 2000
2 150 prod 2 5 5 750
3 400 prod 3 5 87 34800


Hope this help


Regards
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/21/2006 :  04:51:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
SELECT		dbo.tbl_Order.NodeID,
		SUM(dbo.tbl_Order.Quantity) AS Qty,
		dbo.tbl_Products.Text,
		dbo.tbl_Login.CustID,
		MAX(dbo.tbl_Products.Price) Price,
		SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Products.Price) TotalSum
FROM		dbo.tbl_Order
INNER JOIN	dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
INNER JOIN	dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID AND dbo.tbl_Login.CustID = 20
INNER JOIN	dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
WHERE		dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-05-01', 102)
GROUP BY	dbo.tbl_Order.NodeID,
		dbo.tbl_Products.Text,
		dbo.tbl_Login.CustID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 08/21/2006 :  05:01:11  Show Profile  Reply with Quote
Excellent Peter, Thanks :-)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/21/2006 :  07:27:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks for the feed-back!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000